bigquery
group by
group by all
code:groupbyall
WITH t AS (
SELECT 'apple' as name, 'fruit' as category, 100 as price UNION ALL
SELECT 'banana', 'fruit', 200 UNION ALL
SELECT 'curry', 'food', 300 UNION ALL
SELECT 'curry', 'food', 200 UNION ALL
SELECT 'apple', 'fruit', 400
)
SELECT
SUM(price) AS total
,name
,category
FROM t
GROUP BY ALL
grouping sets
code:groupingsets
WITH t AS (
SELECT 'apple' as name, 'fruit' as category, 100 as price UNION ALL
SELECT 'banana', 'fruit', 200 UNION ALL
SELECT 'curry', 'food', 300 UNION ALL
SELECT 'curry', 'food', 200 UNION ALL
SELECT 'apple', 'fruit', 400
)
SELECT
name
,category
,SUM(price) AS total
FROM t
GROUP BY GROUPING SETS (1, 2)
table:groupingsets
name category total
apple 500
fruit 700
banana 200
curry 500
food 500
1 or 2 の組み合わせでグルーピング
grouping sets 2
code:sets2
WITH t AS (
SELECT 'apple' as name, 'fruit' as category, "red" as color, 100 as price UNION ALL
SELECT 'banana', 'fruit', "yellow", 200 UNION ALL
SELECT 'curry', 'food', "yellow", 300 UNION ALL
SELECT 'curry', 'food', "yellow", 200 UNION ALL
SELECT 'apple', 'fruit', "red", 400
)
SELECT
name
,category
,color
,SUM(price) AS total
FROM t
GROUP BY GROUPING SETS ((1, 2), 3)
table:sets2
name category color total
apple fruit 500
red 500
banana fruit 200
yellow 700
curry food 500
1,2 の組み合わせと3の組み合わせがでてくる
grouping sets with rollup
code:rollup
WITH t AS (
SELECT 'apple' as name, 'fruit' as category, "red" as color, 100 as price UNION ALL
SELECT 'banana', 'fruit', "yellow", 200 UNION ALL
SELECT 'curry', 'food', "yellow", 300 UNION ALL
SELECT 'curry', 'food', "yellow", 200 UNION ALL
SELECT 'apple', 'fruit', "red", 400
)
SELECT
name
,category
,color
,SUM(price) AS total
FROM t
GROUP BY GROUPING SETS (rollup(1, 2, 3))
table:rollup
name category color total
1200
apple 500
apple fruit 500
apple fruit red 500
banana 200
banana fruit 200
banana fruit yellow 200
curry 500
curry food 500
curry food yellow 500
gap fill
code:query
CREATE TEMP TABLE device_data AS
SELECT * FROM UNNEST(
ARRAY<STRUCT<device_id INT64, time DATETIME, signal INT64, state STRING>>[
STRUCT(1, DATETIME '2024-01-01', 74, 'INACTIVE'),
STRUCT(1, DATETIME '2024-01-04', 126, 'INACTIVE'),
STRUCT(2, DATETIME '2024-02-01', 77, 'ACTIVE')
--STRUCT(3, DATETIME '2024-03-01 09:37:00', NULL, 'ACTIVE'),
--STRUCT(4, DATETIME '2024-03-15 09:38:01', 80, 'ACTIVE')
]);
with t as (
SELECT *, datetime_trunc(time, month) as date_month
FROM GAP_FILL(
TABLE device_data,
ts_column => 'time',
bucket_width => INTERVAL 1 day,
value_columns => [
('signal', 'null') -- 'linear', 'locf'
]
)
)
select
date_month,
sum(signal) as s
from t
group by 1
ORDER BY date_month
table:test
date_month s
2024-01-01T00:00:00 200
2024-02-01T00:00:00 77
time bucket
ある時間帯の区切りで集計したいときのやつ。例で十分なのでURLのみ
timestamp
code:ts
-- create values with d "2024-01-01 00:00:00 UTC"
with t as (
select
TIMESTAMP("2024-01-01 00:00:00", "UTC") as ts, -- TIMESTAMP("2024-01-01 00:00:00") も同じ
TIMESTAMP("2024-01-01 00:00:00", "Asia/Tokyo") as ts_as_jst,
)
select
ts,
TIMESTAMP(DATETIME(ts, "Asia/Tokyo")) as ts_jst,
TIMESTAMP(DATETIME(TIMESTAMP_SECONDS(1704067200), "Asia/Tokyo")) as tss_jst, -- select UNIX_SECONDS("2024-01-01 00:00:00")
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', ts, 'Japan') AS ts_to_jst_f,
ts_as_jst,
timestamp_trunc(ts, day, 'Asia/Tokyo') as ts_as_jst_f, -- jst での表記 = ts_as_jst
--DATETIME(dt, "Asia/Tokyo") as dt_jst,
from t
ref.
group by
gap fill