在甲骨文每周分组

标志库拉特纳

我需要按周从oracle SQL进行分组，我在下表中包含数量，该表分为几个类别。我需要获取每个类别的每周数量总和。

源表：quantity_details

date (mm/dd/yyyy)   quantity    category
10/1/2018             4            A
10/2/2018             4            B
10/3/2018             5            C
10/4/2018             7            A
10/5/2018             2            A
10/6/2018             2            B
10/7/2018             1            C
10/8/2018             0            C
10/9/2018             8            C
10/10/2018            2            B
10/11/2018            4            D
10/12/2018            6            B
10/13/2018            8            D
10/14/2018            9            C
10/15/2018            11           A

结果表应如下所示：

week start date (dd/mm/yyyy)    category    sum of quantity
01/10/2018 to 07/10/2018            A            13
01/10/2018 to 07/10/2018            B             6
01/10/2018 to 07/10/2018            C             6
01/10/2018 to 07/10/2018            D             0
08/10/2018 to 14/10/2018            A             0
08/10/2018 to 14/10/2018            B             8
08/10/2018 to 14/10/2018            C            17
08/10/2018 to 14/10/2018            D            12
15/10/2018 to 21/10/2018            A            11
15/10/2018 to 21/10/2018            B             0
15/10/2018 to 21/10/2018            C             0
15/10/2018 to 21/10/2018            D             0

巴巴罗斯·奥赞

您可以使用：

select to_char(trunc(min(min_Date),'iw'),'dd/mm/yyyy')||' to '
     ||to_char(trunc(min(min_Date),'iw')+6,'dd/mm/yyyy') as week,
    as week,
       category, sum(sum_of_quantity) as sum_of_quantity
  from
(
  select to_char(myDate,'iw') as week, min(myDate) as min_Date, max(myDate) max_Date, 
         category, sum(quantity) as sum_of_quantity
    from quantity_details
   group by to_char(myDate,'iw'), category
) 
group by week, category
order by week, category;

WEEK                     CATEGORY  SUM_OF_QUANTITY
------------------------ --------  ---------------
01/10/2018 to 07/10/2018    A            13
01/10/2018 to 07/10/2018    B             6
01/10/2018 to 07/10/2018    C             6
08/10/2018 to 14/10/2018    B             8
08/10/2018 to 14/10/2018    C            17
08/10/2018 to 14/10/2018    D            12
15/10/2018 to 21/10/2018    A            11

SQL小提琴演示1

如果您希望将所有类别及其数量包含为zero，即使它们不匹配，我们也应该更加努力地为left joinas做贡献：

select to_char(trunc(q1.min_Date,'iw'),'dd/mm/yyyy')||' to '
     ||to_char(trunc(q1.min_Date,'iw')+6,'dd/mm/yyyy') as week,
       q1.category,
       nvl(q2.quantity,0) as sum_of_quantity
  from
( 
  select to_char(d1.myDate,'iw') as week, min(d1.myDate) as min_Date,
         max(d1.myDate) max_Date, d2.category
    from quantity_details d1 
   cross join ( select category from quantity_details group by category ) d2
   group by to_char(d1.myDate,'iw'), d2.category
) q1
 left join
(
  select to_char(myDate,'iw') as week, min(myDate) as min_Date, max(myDate) max_Date, 
         category, sum(quantity) as quantity
    from quantity_details
   group by to_char(myDate,'iw'), category
) q2 on ( q1.category = q2.category and q1.week = q2.week )
 order by q1.week, q1.category;

WEEK                     CATEGORY  SUM_OF_QUANTITY
------------------------ --------  ---------------
01/10/2018 to 07/10/2018    A            13
01/10/2018 to 07/10/2018    B             6
01/10/2018 to 07/10/2018    C             6
01/10/2018 to 07/10/2018    D             0
08/10/2018 to 14/10/2018    A             0
08/10/2018 to 14/10/2018    B             8
08/10/2018 to 14/10/2018    C            17
08/10/2018 to 14/10/2018    D            12
15/10/2018 to 21/10/2018    A            11
15/10/2018 to 21/10/2018    B             0
15/10/2018 to 21/10/2018    C             0
15/10/2018 to 21/10/2018    D             0

SQL Fiddle演示主要

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

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