oracle—选择sql中按周分组的数据

0yg35tkg  于 2021-07-26  发布在  Java
关注(0)|答案(1)|浏览(398)

我有一个工作查询(oraclesql),它按存储编号对加仑数进行分组,并按类型和百分比列对加仑数进行求和。每个门店编号都有一个不同的转换日期,我从该日期开始汇总数据-

SELECT StoreNbr, 
SUM(CASE WHEN(ClrntSys IN ('844', '84448')) THEN Gallons ELSE 0 END) AS Gallons_844,
SUM(CASE WHEN(ClrntSys ='GIC') THEN Gallons ELSE 0 END) AS Gallons_GIC,
SUM(CASE WHEN(ClrntSys IN ('844', '84448', 'GIC')) THEN Gallons ELSE 0 END) AS Total_Gallons,
CONCAT(CAST((SUM(CASE WHEN(ClrntSys ='GIC') THEN Gallons ELSE 0 END) /
SUM(CASE WHEN(ClrntSys IN ('844', '84448', 'GIC')) THEN Gallons ELSE 0 END)) AS DECIMAL (5,2)) * 100, '%') AS Percent_GIC
FROM MQ_CDS_NETTRAN
WHERE ClrntSys IN ('844', '84448', 'GIC')
AND ((CostCenter = '701104' AND LastTranDate >= DATE '2020-03-10') 
OR (CostCenter = '701109' AND LastTranDate >= DATE '2020-03-04')
OR (CostCenter = '701257' AND LastTranDate >= DATE '2020-03-12'))
GROUP BY StoreNbr
ORDER BY StoreNbr;

我现在还需要每周总结一下,周日是每周的第一天,但我很难理解datepart是如何工作的。为了得到更好的想法,我只尝试用我在网上看到的datepart示例来总结上周的数据,但这不起作用。它给了我“无效的标识符日期部分”-

SELECT DATEPART(week, 5/17/20) AS weekTotal, 
StoreNbr, 
SUM(CASE WHEN(ClrntSys IN ('844', '84448')) THEN Gallons ELSE 0 END) AS Gallons_844,
SUM(CASE WHEN(ClrntSys ='GIC') THEN Gallons ELSE 0 END) AS Gallons_GIC,
SUM(CASE WHEN(ClrntSys IN ('844', '84448', 'GIC')) THEN Gallons ELSE 0 END) AS Total_Gallons,
CONCAT(CAST((SUM(CASE WHEN(ClrntSys ='GIC') THEN Gallons ELSE 0 END) /
SUM(CASE WHEN(ClrntSys IN ('844', '84448', 'GIC')) THEN Gallons ELSE 0 END)) AS DECIMAL (5,2)) * 100, '%') AS Percent_GIC
FROM MQ_CDS_NETTRAN
WHERE ((CostCenter = '701104' AND LastTranDate >= DATE '2020-03-10') 
OR (CostCenter = '701109' AND LastTranDate >= DATE '2020-03-04')
OR (CostCenter = '701257' AND LastTranDate >= DATE '2020-03-12'))
GROUP BY DATEPART(week, 5/17/20), StoreNbr
ORDER BY StoreNbr;

然而,我真正需要的是每周的数据(周日是每周的开始)分别汇总到每个商店的转换日期。有可能吗?除了datepart还有其他更好的方法吗?

webghufk

webghufk1#

抱歉-刚刚注意到你说的是oracle sql,而我的第一个答案是sql server!出现错误的原因是datepart不是oracle函数。相反,您可以简单地计算日期,使用已知的星期日(在db表中已知的第一个日期之前)作为锚定日期:

SELECT
    '30-DEC-2018' as "Known Sunday,
    trunc((sysdate - to_date('30-DEC-2018')) / 7) as "Week Num",
    to_date('30-DEC-2018') 
        + (trunc((sysdate - to_date('30-DEC-2018')) / 7) * 7)"
FROM
    dual

相关问题