oracle分组依据，当值为零时，聚合=0

Question

我正在尝试构建一个查询，但解决它花费了太多时间。

Oracle数据库v18

这是我的表1

//tr>>td style="text align:centre；">4>td style="text align:centre；">4//tr>>td style="text align:centre；">4//tr>

Date1	标记名	值
2021年1月1日0:01
2021年1月1日0:02	a
2021年1月1日0:01
2021年1月1日0:02	b
2021年1月1日0:01
2021年1月1日0:02	c
2021年1月2日0:01
2021年1月2日0:02	a
2021年1月2日0:01
2021年1月2日0:02	b
2021年1月2日0:01
2021年1月2日0:02	c

所以，这当然很容易用UNION ALL来实现。我想你担心的是，你不想两次通读你的表(一次是计算日期/标签聚合，另一次计算日期聚合(。

任何时候，如果希望在多个级别聚合查询结果，至少应该考虑GROUPING SETS功能。

在你的情况下，诀窍不在于多级聚合。相反，您希望第二级聚合(按日期(是在第一级(按日期/标记(计算的聚合的SUM()。

要做到这一点，您可以使用窗口函数，在完成任何聚合之前，通过日期/标记计算AVG()。这使得以后SUM()成为可能。以下是一个工作示例(Oracle12.1(：

-- Create table with test data
create table my_table1 (Date1,  tagname,    Value) AS (
SELECT TO_DATE('01/01/2021 0:01','DD/MM/YYYY HH24:MI'), 'a',    2 FROM DUAL UNION ALL
SELECT TO_DATE('01/01/2021 0:02','DD/MM/YYYY HH24:MI'), 'a',    4 FROM DUAL UNION ALL
SELECT TO_DATE('01/01/2021 0:01','DD/MM/YYYY HH24:MI'), 'b',    2 FROM DUAL UNION ALL
SELECT TO_DATE('01/01/2021 0:02','DD/MM/YYYY HH24:MI'), 'b',    4 FROM DUAL UNION ALL
SELECT TO_DATE('01/01/2021 0:01','DD/MM/YYYY HH24:MI'), 'c',    2 FROM DUAL UNION ALL
SELECT TO_DATE('01/01/2021 0:02','DD/MM/YYYY HH24:MI'), 'c',    4 FROM DUAL UNION ALL
SELECT TO_DATE('02/01/2021 0:01','DD/MM/YYYY HH24:MI'), 'a',    0 FROM DUAL UNION ALL
SELECT TO_DATE('02/01/2021 0:02','DD/MM/YYYY HH24:MI'), 'a',    0 FROM DUAL UNION ALL
SELECT TO_DATE('02/01/2021 0:01','DD/MM/YYYY HH24:MI'), 'b',    2 FROM DUAL UNION ALL
SELECT TO_DATE('02/01/2021 0:02','DD/MM/YYYY HH24:MI'), 'b',    4 FROM DUAL UNION ALL
SELECT TO_DATE('02/01/2021 0:01','DD/MM/YYYY HH24:MI'), 'c',    2 FROM DUAL UNION ALL
SELECT TO_DATE('02/01/2021 0:02','DD/MM/YYYY HH24:MI'), 'c',    4 FROM DUAL
)
;
-- Compute the averages and the use GROUPING SETS to use those those
-- averages conditionally at multiple levels of aggregation
with date_tag_summary as (
select trunc(date1) date1, tagname, avg(value) avg_value
from my_table1
group by trunc(date1), tagname )
select date1, 
case when grouping(tagname)=1 then 'newtag' ELSE tagname END tagname, 
case when grouping(tagname)=1 AND COUNT(DECODE(avg_value,0,1,NULL)) > 0 THEN 0
when grouping(tagname)=1 THEN sum(avg_value)
ELSE min(avg_value) END value
from date_tag_summary
group by grouping sets ( (date1, tagname), (date1) )
order by 1,2;

+-----------+---------+-------+
|   DATE1   | TAGNAME | VALUE |
+-----------+---------+-------+
| 01-JAN-21 | a       |     3 |
| 01-JAN-21 | b       |     3 |
| 01-JAN-21 | c       |     3 |
| 01-JAN-21 | newtag  |     9 |
| 02-JAN-21 | a       |     0 |
| 02-JAN-21 | b       |     3 |
| 02-JAN-21 | c       |     3 |
| 02-JAN-21 | newtag  |     0 |
+-----------+---------+-------+

为了说明数据没有被读取两次，以下是该查询的执行计划：

-----------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |       |       |     6 (100)|          |
|   1 |  SORT ORDER BY        |           |     3 |    63 |     6  (50)| 00:00:01 |
|   2 |   SORT GROUP BY ROLLUP|           |     3 |    63 |     6  (50)| 00:00:01 |
|   3 |    VIEW               |           |     9 |   189 |     4  (25)| 00:00:01 |
|   4 |     HASH GROUP BY     |           |     9 |   117 |     4  (25)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| MY_TABLE1 |    12 |   156 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

一种方法使用cross join生成行，然后引入现有结果：

select d.date1, t.tagname, avg(value) value
from (select distinct to_date(date1, 'dd/MM/yyyy') as date1 from table1
) d cross join
(select 'a' as tagname from dual union all
select 'b' as tagname from dual union all
select 'c' as tagname from dual union all
select 'd' as tagname from dual
) t
table1 t1
on to_date(t1.date1, 'dd/MM/yyyy') = d.date1 and
t1.tagname = t.tagname
group by date1, tagname

您可以使用grouping sets，然后用分析函数计算的平均值之和替换组的平均值总和。

select /*+ gather_plan_statistics */
trunc(date1) as dt
, case grouping_id(tagname)
when 0
then tagname
else 'newtag'
end as tagname
, case grouping_id(tagname)
when 0
then avg(value)
else (
/*Total sum except total avg*/
sum(avg(value)) over(
partition by trunc(date1)
) - avg(value))
* decode(min(avg(value)) over(partition by trunc(date1)), 0, 0, 1)
end as val

from a
group by grouping sets( (trunc(date1), tagname), trunc(date1))

DT|TAGNAME|VAL：--------|：--------|--：2021年1月1日|a|32021年1月1日|b|32021年1月1日|c|32021年1月1日|新标签|92021年1月2日|a|02021年1月2日|b|32021年1月2日| c |32021年1月2日|新标签|0

db<gt；小提琴这里

您可以使用以下查询。当然，它是在SQL 中设置的

;WITH cte AS
(SELECT convert(date,date1) as date1,tagname,avg(value) value
FROM table1
GROUP BY convert(date,date1),tagname)
select date1,tagname,
case when tagname = 'newtag' 
then 
case (select cte.value from cte where cte.date1 = result.date1 and cte.tagname = 'a') 
when 0 then 0 
else (select top 1 sum(c.value) from cte c where convert(date,c.date1,103) = result.date1)
end
else value end
from
(select date1,tagname,value ,ROW_NUMBER() over(partition by date1,tagname order by date1) as seq
from
(
select convert(date,date1) as date1,tagname,avg(value) as value
from table1
group by convert(date,date1),tagname

union all

select convert(date,date1),'newtag', 0
from table1
group by convert(date,date1),tagname
) T
) result
where result.seq = 1
order by convert(date,date1)

第一天平均

with avgday as (select avg(value) value, tagname, to_date(date1,'dd/MM/yyyy') 
from table1 group by date1, tagname)

将行转换为列，并进行案例筛选和操作。

with query1 as (SELECT *  FROM avgday  PIVOT (  MAX(value) FOR tagname IN ('a','b','c')))
select  date1, case 
when   query1.a=0 
then 0 
else a + b + c end value, 
'newtag' tagname
from query1 

我终于想出了一个解决方案，当然这不是最好的答案，但它解决了我的问题