如何从具有两个日期列的表中计算每月的唯一天数,其中期间可能有间隙和重叠?
我宁愿不使用日历表来获取唯一日期,因为它会生成一个包含数千条记录的临时表,并且资源有限。
示例表:
+---------+------------+------------+
| mygroup | alpha | omega |
+---------+------------+------------+
| 1 | 2017-02-04 | 2017-04-14 |
| 1 | 2017-03-25 | 2017-03-28 |
| 1 | 2017-01-23 | 2017-01-25 |
| 2 | 2017-02-05 | 2017-02-20 |
| 1 | 2017-04-28 | 2017-05-12 |
| etc. | etc. | etc. |
+---------+------------+------------+
这是你需要的吗?
select count(distinct selected_date),te.mygroup, MONTHNAME(selected_date)from
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
cross join test te
where selected_date between te.alpha and te.omega
group by mygroup, MONTHNAME(selected_date)
Оutput 为您的示例:
'17','1','April'
'25','1','February'
'3','1','January'
'31','1','March'
'12','1','May'
'16','2','February'
计数可能大于月份中的天数,因为这种重叠存在于几行中 - 这不是错误。
还有另一种方法可以做到这一点,大约比日历表快 10 倍。
最大的资源剧透是日历表本身,它是用来过滤独特日期的。但是,可以在 UINT 中使用 31 位来完成此操作,而不是使用整个表记录。
接收:
- 创建仅包含月份的日历表
- 以月为单位削减周期,并将其与日历表连接起来
- 将句点转换为 UINT 位
- 每月
OR
UINT 以确保唯一性 - 将他们的位计为每月的唯一天数
输出:
+--------------+---------+---------+-------+
| Period | Group 1 | Group 2 | Total |
+--------------+---------+---------+-------+
| 2017 month 5 | 11 | 0 | 11 |
| 2017 month 4 | 15 | 0 | 15 |
| 2017 month 3 | 30 | 0 | 30 |
| 2017 month 2 | 24 | 15 | 39 |
| 2017 month 1 | 2 | 0 | 2 |
+--------------+---------+---------+-------+
MySQL 查询:
SELECT
`tabulate`.`period` AS `Period`,
SUM(IF(`tabulate`.`mygroup` = 1,
`tabulate`.`days`, 0)) AS `Group 1`,
SUM(IF(`tabulate`.`mygroup` = 2,
`tabulate`.`days`, 0)) AS `Group 2`,
SUM(`tabulate`.`days`) AS `Total`
FROM
( SELECT
`unique`.`period`,
BIT_COUNT(BIT_OR(CONV(CONCAT(
REPEAT("1", DAYOFMONTH(`unique`.`omega`) - DAYOFMONTH(`unique`.`alpha`)),
REPEAT("0", DAYOFMONTH(`unique`.`alpha`) - 1)
), 2, 10))) AS `days`,
`unique`.`mygroup`
FROM
( SELECT
DATE_FORMAT(`permonth`.`period_alpha`, "%Y month %c") AS `period`,
GREATEST(`permonth`.`period_alpha`, `permonth`.`example_alpha`) AS `alpha`,
LEAST(`permonth`.`period_omega`, `permonth`.`example_omega`) AS `omega`,
`permonth`.`mygroup`
FROM
( SELECT
`period`.`alpha` AS `period_alpha`,
DATE_SUB(`period`.`omega`, INTERVAL 1 DAY) AS `period_omega`,
`example`.`mygroup`,
IFNULL(`example`.`alpha`, `period`.`alpha`) AS `example_alpha`,
IFNULL(`example`.`omega`, CURDATE()) AS `example_omega`
FROM
( SELECT
DATE_ADD(
MAKEDATE(YEAR(CURDATE()), 1),
INTERVAL `season`.`n` + (`month`.`n` << 2) MONTH
) AS `alpha`,
DATE_ADD(
MAKEDATE(YEAR(CURDATE()), 1),
INTERVAL 1 + `season`.`n` + (`month`.`n` << 2) MONTH
) AS `omega`
FROM
( SELECT 0 AS `n`
UNION ALL SELECT 1
UNION ALL SELECT 2
) AS `month`
CROSS JOIN (SELECT 0 AS `n`
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
) AS `season`
) AS `period`
INNER JOIN
( SELECT 1 AS `mygroup`, "2017-02-04" AS `alpha`, "2017-04-14" AS `omega`
UNION ALL SELECT 1, "2017-03-25", "2017-03-28"
UNION ALL SELECT 1, "2017-01-23", "2017-01-25"
UNION ALL SELECT 2, "2017-02-05", "2017-02-20"
UNION ALL SELECT 1, "2017-04-28", "2017-05-12"
) AS `example` ON (
(`example`.`alpha` < `period`.`omega` OR `example`.`alpha` IS NULL)
AND IFNULL(`example`.`omega`, CURDATE()) >= `period`.`alpha`
)
) AS `permonth`
) AS `unique`
GROUP BY
`unique`.`period`,
`unique`.`mygroup`
) AS `tabulate`
GROUP BY `tabulate`.`period`
ORDER BY `tabulate`.`period` DESC