sql - SQL复杂的不同类型的滚动窗口聚合

Question

我一直在尝试为以下任务创建查询。也许有人可以帮助我。

输入表：

+--------+--------------+------+-----------+-----------+
| Index  | Week_end_day | Type | Num_val_1 | Num_val_2 |
+--------+--------------+------+-----------+-----------+
| D0024A | 30.06.2018   | A    |        10 |         0 |
| D0024A | 30.06.2018   | A    |        20 |         0 |
| D0024A | 30.06.2018   | B    |        30 |         1 |
| A0025A | 30.06.2018   | C    |         5 |         1 |
| A0025A | 30.06.2018   | B    |         4 |         1 |
| A0025A | 30.06.2018   | B    |         6 |         0 |
| D0024A | 14.07.2018   | C    |         5 |         1 |
| D0024A | 14.07.2018   | A    |        10 |         1 |
+--------+--------------+------+-----------+-----------+

我想按周聚合数据，以便将所有数值相加，但同时通过另一个数值列（例如平均值）聚合一些分类值。还需要每周计算总行数并每周计算每种类型。

输出表：

+--------+----------+----------------------+----------------------+----------------------+--------------------------+--------------------------+--------------+---+---------------------+
| Index  | Week_Num | Type_A_avg_Num_val_1 | Type_B_avg_Num_val_1 | Type_C_avg_Num_val_1 | Sum_per_week - Num_val_1 | Sum_per_week - Num_val_2 | Type_A_count | . | total_count_in_week |
+--------+----------+----------------------+----------------------+----------------------+--------------------------+--------------------------+--------------+---+---------------------+
| D0024A |        1 | 15                   | 30                   | 5                    | 60                       | 1                        | 2            | . | 6                   |
| D0024A |        2 | 10                   | 22                   | 10                   | 10                       | 2                        | 1            | . | 4                   |
| D0024A |        3 | 12                   | 14                   | 7                    | 50                       | 0                        | 3            | . | 5                   |
| D0025A |        1 | .                    | .                    | .                    | .                        | .                        | .            | . | .                   |
| D0025A |        2 | .                    | .                    | .                    | .                        | .                        | .            | . | .                   |
+--------+----------+----------------------+----------------------+----------------------+--------------------------+--------------------------+--------------+---+---------------------+

我已经到了这个草稿点：

SELECT
    Index,
    dense_rank() over(order by Week_end_date) AS Week_Num
    SUM(Num_val_1) AS SUM_Num_val_1,
    SUM(Num_val_2) AS SUM_Num_val_2,
    -- need average
    AVG(CASE WHEN Type = 'A' THEN Num_val_1 END) AS TypeA_avg_Num_val_1
    ....

    -- need to count
    SUM(CASE WHEN Type = 'A' THEN 1 END) AS TypeA_count


FROM t1
GROUP BY Index, Week_end_date

你能看看吗？我收到错误。

另外，不知道 - Index 和 Week_end_day 可能未排序是否重要。

标签： sqlsql-serveraggregaterdbms