条件に基づく平均合計

debugcn 投稿 Dev

user3925023

私はこのテーブルとデータサンプルをPostgresデータベースに持っています

CREATE TABLE testAAA(Id integer PRIMARY KEY, datum date, COLA text, COLB text, COLC text, COLD int);

/* Create few records in this table */
INSERT INTO testAAA VALUES(1,to_date('01/01/2018','dd/mm/yyyy'),'PLANTA','VENDORA','OPA','1');
INSERT INTO testAAA VALUES(2,to_date('01/02/2018','dd/mm/yyyy'),'PLANTA','VENDORA','OPA','1');
INSERT INTO testAAA VALUES(3,to_date('01/03/2018','dd/mm/yyyy'),'PLANTA','VENDORA','OPA','1');
INSERT INTO testAAA VALUES(4,null,'PLANTA','VENDORA','OPA','1');
INSERT INTO testAAA VALUES(5,to_date('01/10/2018','dd/mm/yyyy'),'PLANTA','VENDORA','OPA','5');
INSERT INTO testAAA VALUES(6,to_date('01/10/2018','dd/mm/yyyy'),'PLANTA','VENDORA','OPA','10');
INSERT INTO testAAA VALUES(7,to_date('01/11/2018','dd/mm/yyyy'),'PLANTA','VENDORB','OPA','50');
INSERT INTO testAAA VALUES(8,to_date('01/10/2018','dd/mm/yyyy'),'PLANTA','VENDORA','OPA','10');
INSERT INTO testAAA VALUES(9,to_date('01/11/2018','dd/mm/yyyy'),'PLANTA','VENDORA','OPB','5');
COMMIT;

私は条件と組み合わせに基づいて平均合計を作ろうとしています：

SELECT COLA,COLB,COLC,
CASE
  WHEN (datum >= now() - interval '6 month') THEN SUM(COLD)/6
  ELSE '0'
END AS datum_range
FROM testAAA
group by COLA,COLB,COLC;

私の目標は、COLA + COLB + COLCを組み合わせて使用し、DATE間隔条件が満たされた場合に、null値をスキップして、COLD列の過去6か月の平均を生成することです。

現在、次のエラーが発生しています。

Error(s), warning(s):

42803: column "testaaa.datum" must appear in the GROUP BY clause or be used in an aggregate function

上記のエラーを修正するにはどうすればよいですか？

S-マン

デモ：db <>フィドル

SELECT *,
    SUM(COLD) FILTER (WHERE datum >= now() - interval '6 month') OVER (PARTITION BY COLA, COLB, COLC) / 6.0 as avg
FROM testAAA

結果：

| id |      datum |   cola |    colb | colc | cold |                avg |
|----|------------|--------|---------|------|------|--------------------|
|  1 | 2018-01-01 | PLANTA | VENDORA |  OPA |    1 |  4.166666666666667 |
|  2 | 2018-02-01 | PLANTA | VENDORA |  OPA |    1 |  4.166666666666667 |
|  3 | 2018-03-01 | PLANTA | VENDORA |  OPA |    1 |  4.166666666666667 |
|  4 |     (null) | PLANTA | VENDORA |  OPA |    1 |  4.166666666666667 |
|  5 | 2018-10-01 | PLANTA | VENDORA |  OPA |    5 |  4.166666666666667 |
|  6 | 2018-10-01 | PLANTA | VENDORA |  OPA |   10 |  4.166666666666667 |
|  8 | 2018-10-01 | PLANTA | VENDORA |  OPA |   10 |  4.166666666666667 |
|  9 | 2018-11-01 | PLANTA | VENDORA |  OPB |    5 | 0.8333333333333334 |
|  7 | 2018-11-01 | PLANTA | VENDORB |  OPA |   50 |  8.333333333333334 |

[デモ：db <> fiddle] [4]

使用するウィンドウ関数は、 SUM(...) OVER (PARTITION BY ...)あなたが内部のようにSUM集計を行うことができますGROUP句が、元の行を保持しています。集計は別の列として追加されます。
FILTER句を使用すると、集計関数の入力をフィルタリングできます。したがって、次の行のみを集約できます。datum >= ...