Window Function in Google BigQuery (Calculates a running total or aggregate within a partition) Demo
Window Function in Google BigQuery Calculates a running total or aggregate within a partition
======================================================
CREATE TABLE project_id.dataset_id.sales_table (
product_id INT64,
category STRING,
sales FLOAT64,
date DATE
);
INSERT INTO project_id.dataset_id.sales_table (product_id, category, sales, date)
VALUES
(1, 'Electronics', 500.00, '2025-01-01'),
(2, 'Electronics', 300.00, '2025-01-02'),
(3, 'Electronics', 700.00, '2025-01-03'),
(4, 'Furniture', 150.00, '2025-01-01'),
(5, 'Furniture', 200.00, '2025-01-02'),
(6, 'Furniture', 250.00, '2025-01-03'),
(7, 'Clothing', 50.00, '2025-01-01'),
(8, 'Clothing', 60.00, '2025-01-02'),
(9, 'Clothing', 70.00, '2025-01-03'),
(10, 'Electronics', 800.00, '2025-01-04'),
(11, 'Furniture', 300.00, '2025-01-04'),
(12, 'Clothing', 80.00, '2025-01-04');
SELECT *
FROM project_id.dataset_id.sales_table;
=====================================
======================================================================
SELECT
product_id,
category,
sales,
date,
SUM(sales) OVER (ORDER BY date) AS running_total
FROM `logical-cubist-429017-g8.testdataset.sales_table`;
=====================================================================
SELECT
product_id,
category,
sales,
date,
SUM(sales) OVER (Partition by category ORDER BY date) AS running_total
FROM `logical-cubist-429017-g8.testdataset.sales_table`;
========================================
SELECT
product_id,
category,
sales,
date,
SUM(sales) OVER (Partition by category ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM `logical-cubist-429017-g8.testdataset.sales_table`;
===============================================