SUM（累積合計）
WITH
stock_summary AS (
SELECT DATE('2026-01-01') AS date, 1 AS store_id, 100 AS stock_count UNION ALL
SELECT DATE('2026-02-01') AS date, 1 AS store_id, 200 AS stock_count UNION ALL
SELECT DATE('2026-03-01') AS date, 1 AS store_id, 300 AS stock_count UNION ALL
SELECT DATE('2026-04-01') AS date, 2 AS store_id, 400 AS stock_count
)
SELECT
date,
store_id,
stock_count,
SUM (stock_count) OVER (PARTITION BY store_id ORDER BY date) AS total_stock_count
FROM
stock_summary
;
COUNT（累積件数）
WITH
stock_summary AS (
SELECT DATE('2026-01-01') AS date, 1 AS store_id, 100 AS stock_count UNION ALL
SELECT DATE('2026-02-01') AS date, 1 AS store_id, 200 AS stock_count UNION ALL
SELECT DATE('2026-03-01') AS date, 1 AS store_id, 300 AS stock_count UNION ALL
SELECT DATE('2026-04-01') AS date, 2 AS store_id, 400 AS stock_count
)
SELECT
date,
store_id,
stock_count,
COUNT (stock_count) OVER (PARTITION BY store_id ORDER BY date) AS total_record_count
FROM
stock_summary
;
AVG（移動平均）
WITH
stock_summary AS (
SELECT DATE('2026-01-01') AS date, 1 AS store_id, 100 AS stock_count UNION ALL
SELECT DATE('2026-02-01') AS date, 1 AS store_id, 200 AS stock_count UNION ALL
SELECT DATE('2026-03-01') AS date, 1 AS store_id, 300 AS stock_count UNION ALL
SELECT DATE('2026-04-01') AS date, 2 AS store_id, 400 AS stock_count
)
SELECT
date,
store_id,
stock_count,
AVG (stock_count) OVER (PARTITION BY store_id ORDER BY date) AS move_count_average
FROM
stock_summary
;
LAG（前行参照）
WITH
stock_summary AS (
SELECT DATE('2026-01-01') AS date, 1 AS store_id, 100 AS stock_count UNION ALL
SELECT DATE('2026-02-01') AS date, 1 AS store_id, 200 AS stock_count UNION ALL
SELECT DATE('2026-03-01') AS date, 1 AS store_id, 300 AS stock_count UNION ALL
SELECT DATE('2026-04-01') AS date, 2 AS store_id, 400 AS stock_count
)
SELECT
date,
store_id,
stock_count,
LAG (stock_count) OVER (PARTITION BY store_id ORDER BY date) AS prev_stock_count
FROM
stock_summary
;
LEAD（後行参照）
WITH
stock_summary AS (
SELECT DATE('2026-01-01') AS date, 1 AS store_id, 100 AS stock_count UNION ALL
SELECT DATE('2026-02-01') AS date, 1 AS store_id, 200 AS stock_count UNION ALL
SELECT DATE('2026-03-01') AS date, 1 AS store_id, 300 AS stock_count UNION ALL
SELECT DATE('2026-04-01') AS date, 2 AS store_id, 400 AS stock_count
)
SELECT
date,
store_id,
stock_count,
LEAD (stock_count) OVER (PARTITION BY store_id ORDER BY date) AS next_stock_count
FROM
stock_summary
;
