SQL查询如何在Oracle / Hive中获取每周/每年的最后一个工作日？

debugcn 发表于 Dev

用户名

假设我有test_data以下示例数据，每周有1-5天，数据库中有数据（> = 1天，“有数据”，<= 5天）：

    code     vol    val      num     test_date
   --------------------------------------------
1   00001    100    0.1      111      20191104
2   00001    100    0.1      111      20191105
3   00001    100    0.1      111      20191106
4   00001    100    0.1      111      20191107
5   00001    100    0.1      111      20191108
7   00001    100    0.1      111      20191111
8   00001    200    0.1      222      20191112
9   00001    200    0.1      111      20191113
10  00001    400    0.3      222      20191114
11  00001    200    0.2      333      20191118
12  00002    100    0.1      111      20191104
13  00002    200    0.1      222      20191105
14  00002    200    0.1      111      20191106
15  00002    400    0.3      222      20191107
16  00002    200    0.2      333      20191108
....................
....................

我想总结volume，number并value通过周/年code，现在我可以通过下面的SQL查询来概括他们，但根据我不能让一个星期的最后一天test_date，在最后一天可能是一个星期内的任何一天或一年（由于工作/工作日），我们需要显示最后一个日期列

SELECT t.code
        ,date_add(concat_ws('-',substr(t.test_date,1,4),substr(t.test_date,5,2),substr(t.test_date,7,2)) ,
            -pmod(datediff(concat_ws('-',substr(t.test_date,1,4),substr(t.test_date,5,2),substr(t.test_date,7,2)),'1990-01-01'),7)) AS test_date
        ,sum(t.number) AS num
        ,sum(t.volume) AS vol
        ,sum(t.value) AS val
FROM test_data t
GROUP BY t.code, test_date

现在我的输出如下：

    code     vol    val      num     test_date(monday)
   ----------------------------------------------------
1   00001    500    0.5      555      20191104
2   00001    900    0.6      666      20191111
3   00001    200    0.1      111      20191118
4   00001    400    0.3      222      20191125
5   00001    200    0.2      333      20191202

但是我的预期输出如下：

    code     vol    val      num     test_date(the last date of week in database)
   -------------------------------------------------------------------------------
1   00001    500    0.5      555      20191108
2   00001    900    0.6      666      20191114
3   00001    200    0.1      111      20191122
4   00001    400    0.3      222      20191129
5   00001    200    0.2      333      20191206

非常感谢您的任何建议。

拉达加斯特81

我认为以下是您想要的：

SELECT t.code
     , max(t.test_date) AS test_date
     , sum(t.number) AS num
     , sum(t.volume) AS vol
     , sum(t.value)  AS val
FROM test_data t
GROUP BY t.code, TRUNC(TO_DATE(t.test_date,'RRRRMMDD'),'IW')

我只是缩短了方程式，使用TO_DATE和来计算一周中的第一天TRUNC。然后，您只需选择test_date每个组的最大值即可，这是本周中存在数据的最后一天。

如果您只想一周中的最后一天，不管是否有数据，只需将相应的天数添加到开始日期，例如TRUNC(TO_DATE(t.test_date,'RRRRMMDD'),'IW') + 6星期日。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。