在sql中，拉取顺序的每月潜在客户计数为列？

umuewwlo 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(383)

我正试着统计每个公司每月的潜在客户数量。我可以通过以下查询为每个月执行此操作：

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、month1lc、month2lc等等。
我想这可能需要一个嵌入式select函数，但我仍然在学习sql。我怎样才能做到这一点？

mysql mariadb pivot-table

来源：https://stackoverflow.com/questions/48308386/pulling-sequential-monthly-lead-counts-as-columns-in-sql

1条答案

按热度按时间

bzzcjhmw1#

您可以使用“条件聚合”而不是运行多个查询。实际上，您将当前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

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

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

赞(0）回复(0）举报 2021-06-21

我来回答

在sql中，拉取顺序的每月潜在客户计数为列？

1条答案

相关问题

热门标签

最新问答