sql - GROUP BY TRUNC 的链(日期)

Question

我有一个包含每小时数据和值(value)的简单表格。我想计算每个月每日最大值的平均值。
该查询起初看起来很简单:

WITH daily_max AS
(
  SELECT TRUNC(the_date, 'DD') as my_day, MAX(value) AS value 
    FROM my_data 
   GROUP by TRUNC(the_date, 'DD')
)
SELECT trunc(my_day, 'MM'), AVG(value) 
FROM daily_max
GROUP BY trunc(my_day, 'MM')
order by 1
;

但是，我在第一列中有很多“重复”(每天一个):

01/01/2017 00:00:00 95
01/01/2017 00:00:00 90
01/01/2017 00:00:00 99
01/01/2017 00:00:00 96
01/01/2017 00:00:00 94
01/01/2017 00:00:00 97
01/01/2017 00:00:00 96
01/01/2017 00:00:00 86
01/01/2017 00:00:00 98
01/01/2017 00:00:00 98

01/02/2017 00:00:00 97
01/02/2017 00:00:00 93
01/02/2017 00:00:00 100
01/02/2017 00:00:00 98
01/02/2017 00:00:00 94
01/02/2017 00:00:00 99
01/02/2017 00:00:00 94
01/02/2017 00:00:00 95
01/02/2017 00:00:00 99

第一个子查询按预期返回每日最大值。

我怀疑 DATE 数据类型存在奇怪的行为，但即使我在日期上使用 TO_CHAR 函数，我也有相同的行为。 GROUP BY 语句中的表达式如何导致具有相同值的多行？

with daily_max AS
(
  SELECT TRUNC(the_date, 'DD') as my_day, MAX(value) AS value 
    FROM my_data 
   GROUP by TRUNC(the_date, 'DD')
)
SELECT TO_CHAR(trunc(my_day, 'MM')), AVG(value) 
FROM daily_max
GROUP BY TO_CHAR(trunc(my_day, 'MM'))
order by 1
;

为了增加我的困惑，当我在第一个子查询中将日期转换为时间戳时，结果是我所期望的:

with daily_max AS
(
  SELECT CAST(TRUNC(the_date , 'DD') AS timestamp) as my_day, MAX(value) AS value 
    FROM my_data 
   GROUP by TRUNC(the_date , 'DD')
)
SELECT trunc(my_day, 'MM') AS the_month, AVG(value) 
FROM daily_max
GROUP BY trunc(my_day, 'MM')
order by 1
;

01/01/2017 00:00:00 94.9
01/02/2017 00:00:00 95.78571428571428571428571428571428571429
01/03/2017 00:00:00 95.38709677419354838709677419354838709677
01/04/2017 00:00:00 94.9
01/05/2017 00:00:00 95.32258064516129032258064516129032258065
01/06/2017 00:00:00 96.46666666666666666666666666666666666667
01/07/2017 00:00:00 96.16129032258064516129032258064516129032
01/08/2017 00:00:00 96.16129032258064516129032258064516129032
01/09/2017 00:00:00 96.13333333333333333333333333333333333333
01/10/2017 00:00:00 95.87096774193548387096774193548387096774
01/11/2017 00:00:00 97.3
01/12/2017 00:00:00 96.90322580645161290322580645161290322581
01/01/2018 00:00:00 96.43478260869565217391304347826086956522

我可能错过了一些愚蠢的事情，但是有人可以向我解释这些行为吗？

查询生成测试表:

CREATE TABLE my_data 
AS
SELECT TRUNC (SYSDATE - ROWNUM/24, 'HH') as the_date, ROUND(DBMS_RANDOM.value(0,100),0) AS value
  FROM DUAL 
  CONNECT BY ROWNUM < 366*24
  ;

Answer 1

这似乎是 bug 20537092 ;它在 12.1.0.2(使用 CTE 或内联 View )中可重现，但在 11.2.0.4 或 12.2.0.1 中可重现。

该文档中的解决方法似乎可以解决此问题；设置后运行您的示例

alter session set "_optimizer_aggr_groupby_elim"=false;

在以前没有的 12.1 session 中给出了合理的结果:

TRUNC(MY_DAY,'MM')  AVG(VALUE)
------------------- ----------
2017-01-01 00:00:00       95.5
2017-02-01 00:00:00 95.6428571
2017-03-01 00:00:00 95.3225806
2017-04-01 00:00:00 95.6666667
2017-05-01 00:00:00 97.0322581
2017-06-01 00:00:00       95.7
2017-07-01 00:00:00 95.0967742
2017-08-01 00:00:00 96.1935484
2017-09-01 00:00:00 94.9333333
2017-10-01 00:00:00         96
2017-11-01 00:00:00 96.9333333
2017-12-01 00:00:00 95.3870968
2018-01-01 00:00:00 95.0434783

重写查询以避免嵌套的 group-by 可能更实用 - 当然取决于您真正的复杂程度，以及您是否可以修改相关 session 或数据库初始化设置，或修补它。

对于您的(可能是简化的)示例，在没有应用解决方法的新 session 中，用不同的和分析版本替换内部聚合/分组似乎有效；虽然它有点难看，可能对你的实际情况不实用:

WITH daily_max AS
(
  SELECT DISTINCT TRUNC(the_date, 'DD') as my_day,
         MAX(value) OVER (PARTITION BY TRUNC(the_date, 'DD')) AS value
    FROM my_data 
)
SELECT trunc(my_day, 'MM'), AVG(value) 
FROM daily_max
GROUP BY trunc(my_day, 'MM')
order by 1
;

和往常一样，仅仅因为它看起来像这个错误并不意味着它一定是；您可能需要提出服务请求以获得确认，尤其是在修补之前。