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
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
|
FROM Produce;
FROM (SELECT 'apples' AS item, 2 AS sales)
|> SELECT item AS fruit_name;
FROM (SELECT 'apples' AS item, 2 AS sales)
|> EXTEND item IN ('carrots', 'oranges') AS is_orange;
FROM (SELECT 2 AS x, 3 AS y) AS t
|> SET x = x * x, y = 8
|> SELECT t.x AS original_x, x, y;
FROM (SELECT 1 AS x, 2 AS y) AS t
|> DROP x
|> SELECT t.x AS original_x, y;
FROM (SELECT 1 AS x, 2 AS y) AS t
|> RENAME y AS renamed_y
|> SELECT *, t.y AS t_y;
FROM (SELECT 1 AS x, 2 AS y)
|> AS t
|> RENAME y AS renamed_y
|> SELECT *, t.y AS t_y;
FROM foo
|> WHERE sales >= 3
|> LIMIT 10 OFFSET 4
|> ORDER BY sales DESC;
FROM Produce
|> AGGREGATE SUM(sales) AS total_sales
GROUP AND ORDER BY category, item DESC;
FROM Produce
|> AGGREGATE SUM(sales) AS total_sales
GROUP BY category, item
|> ORDER BY category, item DESC;
FROM Produce
|> AGGREGATE SUM(sales) AS total_sales ASC
GROUP BY item, category DESC;
FROM Produce
|> AGGREGATE SUM(sales) AS total_sales
GROUP BY item, category
|> ORDER BY category DESC, total_sales;
FROM foo
|> UNION ALL
(SELECT 1),
(SELECT 2);
FROM foo
|> UNION DISTINCT
(SELECT 1),
(SELECT 2);
FROM foo
|> UNION ALL BY NAME
(SELECT 20 AS two_digit, 2 AS one_digit);
FROM foo
|> INTERSECT DISTINCT
(SELECT 1),
(SELECT 2);
FROM foo
|> INTERSECT DISTINCT
(SELECT * FROM UNNEST(ARRAY<INT64>[2, 3, 3, 5]) AS number),
(SELECT * FROM UNNEST(ARRAY<INT64>[3, 3, 4, 5]) AS number);
FROM foo
|> EXCEPT DISTINCT
(SELECT 1),
(SELECT 2);
FROM foo
|> EXCEPT DISTINCT
(SELECT * FROM UNNEST(ARRAY<INT64>[1, 2]) AS number),
(SELECT * FROM UNNEST(ARRAY<INT64>[1, 4]) AS number);
FROM foo
|> EXCEPT DISTINCT BY NAME
(SELECT 10 AS two_digit, 1 AS one_digit);
FROM foo
|> LEFT JOIN
(
SELECT "apples" AS item, 123 AS id
) AS produce_data
ON produce_sales.item = produce_data.item
|> SELECT produce_sales.item, sales, id;
FROM input_table
|> CALL tvf1(arg1)
|> CALL tvf2(arg2, arg3);
FROM mydataset.Produce
|> CALL APPENDS(NULL, NULL);
FROM LargeTable
|> TABLESAMPLE SYSTEM (1 PERCENT);
FROM foo
|> PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2'));
FROM foo
|> UNPIVOT(sales FOR quarter IN (Q1, Q2));
SELECT * FROM UNNEST(ARRAY<INT64>[1, 2, 3]) AS number
|> UNION DISTINCT
(SELECT 1),
(SELECT 2);
WITH
NumbersTable AS (
SELECT 1 AS one_digit, 10 AS two_digit
UNION ALL
SELECT 2, 20
UNION ALL
SELECT 3, 30
)
SELECT one_digit, two_digit FROM NumbersTable
|> INTERSECT DISTINCT BY NAME
(SELECT 10 AS two_digit, 1 AS one_digit);
(
SELECT 'apples' AS item, 2 AS sales
UNION ALL
SELECT 'bananas' AS item, 5 AS sales
UNION ALL
SELECT 'apples' AS item, 7 AS sales
)
|> AGGREGATE COUNT(*) AS num_items, SUM(sales) AS total_sales;
(
SELECT "000123" AS id, "apples" AS item, 2 AS sales
UNION ALL
SELECT "000456" AS id, "bananas" AS item, 5 AS sales
) AS sales_table
|> AGGREGATE SUM(sales) AS total_sales GROUP BY id, item
-- The sales_table alias is now out of scope. We must introduce a new one.
|> AS t1
|> JOIN (SELECT 456 AS id, "yellow" AS color) AS t2
ON CAST(t1.id AS INT64) = t2.id
|> SELECT t2.id, total_sales, color;
SELECT 1 AS x, 2 AS y, 3 AS z
|> AS t
|> RENAME y AS renamed_y
|> SELECT *, t.y AS t_y;
(
SELECT 'apples' AS item, 2 AS sales
UNION ALL
SELECT 'carrots' AS item, 8 AS sales
)
|> EXTEND item IN ('carrots', 'oranges') AS is_orange;
WITH bar AS (
FROM foo
)
SELECT * FROM bar;
|
