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

我正努力为每家公司争取每月的销售线索。我可以在任何一个月内使用以下查询：

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、Month 1 LC、month2lc等等。
我想这可能需要一个嵌入的select函数，但我仍在动态学习SQL。我怎样才能做到这一点？

最佳答案

可以使用“条件聚合”而不是运行多个查询。实际上，您可以在聚合函数中移动当前where条件以形成case expression。注意，count()函数忽略空值

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

关于mysql - 将连续的每月潜在客户计数作为SQL中的列？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/48308386/