SQL 组按另一个表中的日期范围列聚合

Question

我需要一个查询来按另一个表中的日期范围对一个表中的聚合进行分组。

Table 1
 weeknumber | weekyear | weekstart  |  weekend
------------+----------+------------+------------
         18 |     2016 | 2016-02-01 | 2016-02-08
         19 |     2016 | 2016-02-08 | 2016-02-15
         20 |     2016 | 2016-02-15 | 2016-02-22
         21 |     2016 | 2016-02-22 | 2016-02-29
         22 |     2016 | 2016-02-29 | 2016-03-07
         23 |     2016 | 2016-03-07 | 2016-03-14
         24 |     2016 | 2016-03-14 | 2016-03-21
         25 |     2016 | 2016-03-21 | 2016-03-28
         26 |     2016 | 2016-03-28 | 2016-04-04
         27 |     2016 | 2016-04-04 | 2016-04-11
         28 |     2016 | 2016-04-11 | 2016-04-18
         29 |     2016 | 2016-04-18 | 2016-04-25
         30 |     2016 | 2016-04-25 | 2016-05-02
         31 |     2016 | 2016-05-02 | 2016-05-09
         32 |     2016 | 2016-05-09 | 2016-05-16
         33 |     2016 | 2016-05-16 | 2016-05-23
         34 |     2016 | 2016-05-23 | 2016-05-30
         35 |     2016 | 2016-05-30 | 2016-06-06
         36 |     2016 | 2016-06-06 | 2016-06-13
         37 |     2016 | 2016-06-13 | 2016-06-20
         38 |     2016 | 2016-06-20 | 2016-06-27
         39 |     2016 | 2016-06-27 | 2016-07-04
         40 |     2016 | 2016-07-04 | 2016-07-11
         41 |     2016 | 2016-07-11 | 2016-07-18
         42 |     2016 | 2016-07-18 | 2016-07-25
         43 |     2016 | 2016-07-25 | 2016-08-01
         44 |     2016 | 2016-08-01 | 2016-08-08
         45 |     2016 | 2016-08-08 | 2016-08-15
         46 |     2016 | 2016-08-15 | 2016-08-22
         47 |     2016 | 2016-08-22 | 2016-08-29
         48 |     2016 | 2016-08-29 | 2016-09-05
         49 |     2016 | 2016-09-05 | 2016-09-12
Table 2
 accountid |   rdate    | fee1 | fee2 | fee3 | fee4
-----------+------------+------+------+------+------
    481164 | 2015-12-22 |    8 |    1 |    5 |    1
    481164 | 2002-12-22 |    1 |    0 |    0 |    0
    481166 | 2015-12-22 |    1 |    0 |    0 |    0
    481166 | 2016-10-20 |   14 |    0 |    0 |    0
    481166 | 2016-10-02 |    5 |    0 |    0 |    0
    481166 | 2016-01-06 |   18 |    4 |    0 |    5
    482136 | 2016-07-04 |   18 |    0 |    0 |    0
    481164 | 2016-07-04 |    2 |    3 |    4 |    5
    481164 | 2016-06-28 |   34 |    0 |    0 |    0
    481166 | 2016-07-20 |   50 |    0 |    0 |   69
    481166 | 2016-07-13 |   16 |    0 |    0 |    5
    481166 | 2016-09-15 |    8 |    0 |    0 |    2
    481166 | 2016-10-03 |    8 |    0 |    0 |    0

我需要为表 1 中每个日期范围(周开始、周末)中的日期汇总 fee1+fee2+fee3+fee4，然后按 accountid 分组。像这样:

 accountid |  weekstart |   weekend  | SUM 
-----------+------------+------------+------
    481164 | 2016-02-01 | 2016-02-08 |  69 
    481166 | 2016-02-01 | 2016-02-08 |  44
    481164 | 2016-02-08 | 2016-02-15 |  22 
    481166 | 2016-02-08 | 2016-02-15 |  12

Answer 1

select accountid, weekstart, weekend, 
       sum(fee1 + fee2 + fee3 + fee4) as total_fee
from table2
     inner join table1 on table2.rdate >= table1.weekstart and table2.rdate < table1.weekend 
group by accountid, weekstart, weekend;

只是一件事:

 weeknumber | weekyear | weekstart  |  weekend
------------+----------+------------+------------
         18 |     2016 | 2016-02-01 | 2016-02-08
         19 |     2016 | 2016-02-08 | 2016-02-15

第 18 周的周末应该是 2016-02-07，因为 2016-02-08 是第 19 周的开始。

 weeknumber | weekyear | weekstart  |  weekend
------------+----------+------------+------------
         18 |     2016 | 2016-02-01 | 2016-02-07
         19 |     2016 | 2016-02-08 | 2016-02-14

在这里查看:http://rextester.com/NCBO56250