-
Notifications
You must be signed in to change notification settings - Fork 42
/
evaluate-boolean-expression.sql
105 lines (93 loc) · 3.52 KB
/
evaluate-boolean-expression.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
/*
Table Variables:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| name | varchar |
| value | int |
+---------------+---------+
name is the primary key for this table.
This table contains the stored variables and their values.
Table Expressions:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| left_operand | varchar |
| operator | enum |
| right_operand | varchar |
+---------------+---------+
(left_operand, operator, right_operand) is the primary key for this table.
This table contains a boolean expression that should be evaluated.
operator is an enum that takes one of the values ('<', '>', '=')
The values of left_operand and right_operand are guaranteed to be in the Variables table.
Write an SQL query to evaluate the boolean expressions in Expressions table.
Return the result table in any order.
The query result format is in the following example.
Variables table:
+------+-------+
| name | value |
+------+-------+
| x | 66 |
| y | 77 |
+------+-------+
Expressions table:
+--------------+----------+---------------+
| left_operand | operator | right_operand |
+--------------+----------+---------------+
| x | > | y |
| x | < | y |
| x | = | y |
| y | > | x |
| y | < | x |
| x | = | x |
+--------------+----------+---------------+
Result table:
+--------------+----------+---------------+-------+
| left_operand | operator | right_operand | value |
+--------------+----------+---------------+-------+
| x | > | y | false |
| x | < | y | true |
| x | = | y | false |
| y | > | x | true |
| y | < | x | false |
| x | = | x | true |
+--------------+----------+---------------+-------+
As shown, you need find the value of each boolean exprssion in the table using the variables table.
*/
# V0
SELECT left_operand, operator, right_operand,
CASE WHEN operator = '>' AND v1.value > v2.value THEN 'true'
WHEN operator = '<' AND v1.value < v2.value THEN 'true'
WHEN operator = '=' AND v1.value = v2.value THEN 'true'
ELSE 'false' END AS value
FROM expressions e
LEFT JOIN variables v1
ON e.left_operand = v1.name
LEFT JOIN variables v2
ON e.right_operand = v2.name;
# V1
# https://code.dennyzhang.com/evaluate-boolean-expression
select t.left_operand, t.operator, t.right_operand,
(case when v1_value>v2.value and operator = '>' then "true"
when v1_value<v2.value and operator = '<' then "true"
when v1_value=v2.value and operator = '=' then "true"
else "false"
end) as value
from
(select e.*, v1.value as v1_value
from Expressions as e inner join Variables as v1
on e.left_operand = v1.name) as t inner join Variables as v2
on t.right_operand = v2.name
# V2
# Time: O(n)
# Space: O(n)
SELECT left_operand, operator, right_operand,
CASE WHEN operator = '>' AND v1.value > v2.value THEN 'true'
WHEN operator = '<' AND v1.value < v2.value THEN 'true'
WHEN operator = '=' AND v1.value = v2.value THEN 'true'
ELSE 'false' END AS value
FROM expressions e
LEFT JOIN variables v1
ON e.left_operand = v1.name
LEFT JOIN variables v2
ON e.right_operand = v2.name;