mysql - 将连续的每月潜在客户计数作为 SQL 中的列？

Question

我正在尝试获取每家公司的每月潜在客户数量。我可以通过这些查询对任何一个月执行此操作:

MONTH 1 LEAD COUNTS
select l.companyProfileID, count(l.id) as 'Month 1 LC'
from lead l
join companyProfile cp on cp.id = l.companyProfileID
where l.createTimestamp between cp.createTimestamp and date_sub(cp.createTimestamp, INTERVAL -1 month)
group by companyProfileID

MONTH 2 LEAD COUNTS
select l.companyProfileID, count(l.id) as 'Month 2 LC'
from lead l
join companyProfile cp on cp.id = l.companyProfileID
where l.createTimestamp between date_sub(cp.createTimestamp, INTERVAL -1 month) and date_sub(cp.createTimestamp, INTERVAL -2 month)
group by companyProfileID

但我不想运行 12 个不同的查询来获得一年的潜在客户数量，而是生成一个包含以下列的表:companyProfileID、第 1 个月 LC、第 2 个月 LC 等。

我想这可能需要一个嵌入式选择功能，但我仍在即时学习 SQL。我怎样才能做到这一点？

Answer 1

您可以使用“条件聚合”而不是运行多个查询。实际上，您将当前的 where 条件移入聚合函数以形成 case 表达式。请注意，count() 函数会忽略 NULL

select 
        l.companyProfileID
      , count(case when l.createTimestamp between cp.createTimestamp 
                    and date_sub(cp.createTimestamp, INTERVAL -1 month) then 1 end) as 'Month 1 LC'
      , count(case when l.createTimestamp between date_sub(cp.createTimestamp, INTERVAL -1 month)
                    and date_sub(cp.createTimestamp, INTERVAL -2 month) then 1 end) as 'Month 2 LC'

      ... more (similar to the above)

      , count(case when l.createTimestamp between date_sub(cp.createTimestamp, INTERVAL -11 month)
                    and date_sub(cp.createTimestamp, INTERVAL -12 month) then 1 end) as 'Month 12 LC'
from lead l
join companyProfile cp on cp.id = l.companyProfileID
where l.createTimestamp between cp.createTimestamp and date_sub(cp.createTimestamp, INTERVAL -12 month)
group by companyProfileID

另请注意，“between”要求第一个日期早于第二个日期，例如以下不会返回行:

select * from t where datecol between 2018-01-01 and 2017-01-01

但是这会起作用:

select * from t where datecol between 2017-01-01 and 2018-01-01