如何避免列组织数据处理和行组织数据处理之间的转换

Question

我正在使用列组织表开发DB2Blu。

我的数据集如下：

Day month year value
------- -------
20200101 202001 2020 100
20200102 202001 2020 110
...
20200215 202002 2020 120

我想按周、月和年汇总这个结果：

Id value
2020 12000
202001 4000 'january
202002 4000 'february
2020001 700 'first week of 2020

为了做到这一点，我还有d_tps 表

Type Id week month year
J 20200101 2020001 202001 2020
J 20200102 2020001 202001 2020
...
J 20200215 2020007 202002 2020
M 202001 null 202001 2020
M 202002 null 202002 2020
Y 2020 null null 2020

我的方法是以下

select d.id, sum(value) from tab1
Inner join d_tps d
On d.id = tab1.year
Or d.id = tab1.month
Or d.id = tab1.year
group by d.id

它起作用并返回预期结果。不幸的是，在查询计划中，带有OR的联接条件会导致CTQ运算符提前到来，并且大多数查询(实际上更复杂(被视为行而不是列。

如何优化它？

Answer 1

看起来一个join条件和聚合就足够了：

select d.week, sum(value)
from tab1 Inner join
d_tps d
On d.id = tab1.day
group by d.week

如果要按多个时间级别进行聚合，请使用grouping sets:

select d.week, d.month, d.year, sum(value)
from tab1 Inner join
d_tps d
On d.id = tab1.day
group by grouping sets ((d.week), (d.month), (d.year))

Answer 2

您应该使用GROUP BY GROUPING SETS&GROUPING功能来实现您想要的。

WITH T (day, month, year, value) AS 
(
values 
(20200101, 202001, 2020, 100)
, (20200102, 202001, 2020, 110)
, (20200215, 202002, 2020, 120)
)
SELECT 
CASE 
WHEN GROUPING(DAY)   = 0 THEN DAY
WHEN GROUPING(MONTH) = 0 THEN MONTH
WHEN GROUPING(YEAR ) = 0 THEN YEAR
END AS ID
, SUM(VALUE) AS VALUE
FROM T
GROUP BY GROUPING SETS (DAY, MONTH, YEAR);

结果是：

|ID         |VALUE      |
|-----------|-----------|
|2020       |330        |
|202001     |210        |
|202002     |120        |
|20200101   |100        |
|20200102   |110        |
|20200215   |120        |