mysql - 使用两个日期列的 COUNT 按周汇总

Question

我想按周汇总一些 COUNT 个数据。一周从周日开始。查询必须考虑 COUNT 为 0 的周数。计数是在两个日期。推荐日期和服务日期。我想要做的就是计算每周的推荐数量以及同一周内有多少个服务日期。这两个 COUNTS 没有关联。转介可能会在 1 周内到来，但服务会在另一周内完成。

我的尝试:1) 我创建了一个日历表，其中包含我工作期间的每一天的条目。日期条目涵盖的时间超过了正在使用的时间段。2) 我的 Select 中有一个字段，按“周开始”进行总结3) 我在每个日期字段(推荐、访问)上都有 COUNT 个字段。请注意，我使用 Distinct 是因为一次访问有多个服务代码，但我只需要将它们计为 1。

代码如下:

select
STR_TO_DATE(DATE_FORMAT(FROM_DAYS(TO_DAYS(calendar.datefield) - MOD(TO_DAYS(calendar.datefield) -1, 7)),'%m/%d/%Y'), '%m/%d/%Y' ) AS `Week Beginning`,

COUNT(DISTINCT opsData.`Patient Last Name`, opsData.`Patient First Name`, opsData.Discipline, opsData.`Referral Date`) as `Referrals`,

COUNT(DISTINCT opsData.`Patient Last Name`, opsData.`Patient First Name`, opsData.`Date of Service`) as Visits

from opsdata RIGHT JOIN calendar ON (DATE(opsData.`Referral Date`) = calendar.datefield)

where (calendar.datefield BETWEEN (SELECT MIN(DATE(opsData.`Referral Date`)) FROM opsdata) AND (SELECT MAX(DATE(opsData.`Referral Date`)) FROM opsdata))
group by `Week Beginning`
order by `Week Beginning` ASC

---

我遇到的问题是，基于 RIGHT JOIN，我一次只能获得准确的 COUNTS 之一。如果我使用 DATE(opsData.Referral Date)，那么 Referral COUNT 是正确的，而 Visits 是错误的。如果我使用 DATE(opsData.Date of Service)，那么 Visits 是正确的，而 Referral COUNT 是错误的。

有什么想法吗？

Answer 1

您需要使用 2 个字段的最早/最晚日期。您可以使用 least() and greatest() functions实现这一目标:

...BETWEEN (SELECT least(MIN(DATE(opsData.`Referral Date`)),min(date(opsData.`Date of Service`))) FROM opsdata)
   AND (SELECT greatest(MAX(DATE(opsData.`Referral Date`)), max(date(opsData.`Date of Service`))) FROM opsdata))