mysql - 如何在MySql或R中获取天数桶的计数

Question

我正在使用两个表，例如:

T1

ID       Name       Insertion_Date

TT-1     ABC        2019-09-22 14:22:18
TT-2     XYZ        2019-08-28 15:12:11

T2

id       Date            value            type   id_2
TT-1     2019-09-03      10,100           A      1001
TT-1     2019-07-12      10,100           A      1002
TT-1     2018-04-18      50,000           A      1003
TT-1     2019-04-03      55,000           B      1004
TT-2     2019-09-01      11,500           A      1005
TT-2     2019-05-22      12,100           B      1006
TT-2     2018-08-30      12,000           B      1007
TT-2     2017-06-12      15,000           C      1008

我正在尝试连接两个表并尝试使用 MySQL 获取下面提到的输出。

其中，变量6month包含ID TT-1在Insertion_Date到180天或六个月之间的条目数， 12month 和 >12month 也是如此。

Total 列包含 T2 中 ID 的总数。

输出:

ID       Name        6month     12month      >12month   Total
TT-1     ABC           3           0             1        4
TT-2     XYZ           2           0             2        4

示例数据框:

id       Date            value            type   id_2    Insertion_Date
TT-1     2019-09-03      10,100           A      1001    2019-09-22 14:22:18
TT-1     2019-07-12      10,100           A      1002    2019-09-22 14:22:18
TT-1     2018-04-18      50,000           A      1003    2019-09-22 14:22:18
TT-1     2019-04-03      55,000           B      1004    2019-09-22 14:22:18
TT-2     2019-09-01      11,500           A      1005    2019-08-28 15:12:11
TT-2     2019-05-22      12,100           B      1006    2019-08-28 15:12:11
TT-2     2018-08-30      12,000           B      1007    2019-08-28 15:12:11
TT-2     2017-06-12      15,000           C      1008    2019-08-28 15:12:11

Answer 1

您也可以根据 select 语句中的某些条件进行聚合。

SELECT 
    t1.ID, 
    t1.Name, 
    SUM(IF(t2.Date > CURRENT_DATE - INTERVAL 6 MONTH AND t2.Date <= CURRENT_DATE, 1 , 0)) AS '6Month',
    SUM(IF(t2.Date > CURRENT_DATE - INTERVAL 12 MONTH AND t2.Date <= CURRENT_DATE - INTERVAL 6 MONTH, 1 , 0)) AS '12Month',
    SUM(IF(t2.Date <= CURRENT_DATE - INTERVAL 12 MONTH , 1 , 0)) AS '>12month',
    COUNT(0) AS Total
FROM t1
INNER JOIN t2 ON t2.id = t1.ID
GROUP BY t1.id, t1.Name;