--テーブル作成 CREATE TABLE public.tbl_sales(store_id varchar(20), date date, sales int); --データ挿入 INSERT INTO tbl_sales VALUES('B1','2019-01-02',1496); INSERT INTO tbl_sales VALUES('C1','2019-01-19',1867); INSERT INTO tbl_sales VALUES('C1','2019-01-22',1254); INSERT INTO tbl_sales VALUES('A1','2019-01-10',1244); INSERT INTO tbl_sales VALUES('A1','2019-01-11',1424); INSERT INTO tbl_sales VALUES('B1','2019-01-04',812 ); INSERT INTO tbl_sales VALUES('A1','2019-01-09',2253); INSERT INTO tbl_sales VALUES('B1','2019-01-01',1514); INSERT INTO tbl_sales VALUES('C1','2019-01-21',2079); COMMIT; --売上累計を求める SELECT store_id ,date ,sales ,SUM(sales) OVER(PARTITION BY store_id ORDER BY date) AS sum_sales FROM tbl_sales ORDER BY date ; --売上累計を求める(ROWSを使用:処理範囲→一つ前の行から現在行) SELECT store_id ,date ,sales ,SUM(sales) OVER( PARTITION BY store_id ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) AS sum_sales FROM tbl_sales ORDER BY date ; --売上累計を求める(RANGEを使用:処理範囲→現在行の日付までの直近の二日) SELECT store_id, date, sales, SUM(sales) OVER( PARTITION BY store_id ORDER BY date RANGE BETWEEN '1 DAYS' PRECEDING AND CURRENT ROW) AS sum_sales FROM tbl_sales ORDER BY date; --売上の累計・最大値・最小値・平均値を合わせて表示(分析関数ごとにウィンドウを定義) SELECT store_id ,date ,sales ,SUM(sales) OVER(PARTITION BY store_id ORDER BY date) AS sum_sales ,MAX(sales) OVER(PARTITION BY store_id ORDER BY date) AS sum_sales ,MIN(sales) OVER(PARTITION BY store_id ORDER BY date) AS sum_sales ,AVG(sales) OVER(PARTITION BY store_id ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS avg_sales FROM tbl_sales ORDER BY date ; --売上の累計・最大値・最小値・平均値を合わせて表示(ウィンドウ名を作成) SELECT store_id ,date ,sales ,SUM(sales) OVER(win_store_id) AS sum_sales ,MAX(sales) OVER(win_store_id) AS sum_sales ,MIN(sales) OVER(win_store_id) AS sum_sales ,AVG(sales) OVER(win_store_id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS avg_sales FROM tbl_sales WINDOW win_store_id AS (PARTITION BY store_id ORDER BY date) ORDER BY date ;