我有一个处理赔偿金的公司的数据库。我必须。我正在处理三个表：结论、m_email 和 m_lead。在“结论”表中，我有：id 和创建日期。在 m_email 表中，我有：id，打开了多少，点击了多少。在 m_lead 表中：id、conclusion_id、creation_date。我需要检查以下内容： 1. 将一年分为四个部分，并检查从 1 月到 4 月、从 4 月到 7 月等有多少申请。 2. 接下来我需要附上一张表格以获得类似的信息。例如：
ID = 1
number_of_aplications = 5000
number_of_leads = 7000
下面我放了我的代码。我有两个问题： 1. 我不知道如何从三个月中添加几个月才能有一个结果。2.我不知道如何将潜在客户的数量连接到此代码
select date_part('month', creating_date) as "1-3", count(id) as
"Number of applications"
from applications
where date_part('month', creating_date) between '01' and '03' AND
date_part('year', creating_date) between '2017' and '2018'
group by date_part('month', creating_date)
order by count(id) DESC ;
你的要求对我来说有点难以理解。但是对于您问题的第一部分，我想我可以回答：
初始情况
您有一个表“应用程序（id int 主键，creation_date 时间戳）”
你需要什么（我的理解）
有每三个月的申请次数（一月到三月等）
一个可能的解决方案
select a.delta, sum(applicationByMonth) as applicationByMonth, sum(leadByMonth) as leadByMonth
from
(
select '1 trimester' as delta, cast(date_part('month', creation_date) as int) as month, count(id) as applicationByMonth
from applications
where cast(date_part('month', creation_date) as int) <= 3
group by cast(date_part('month', creation_date) as int)
union
select '2 trimester' as delta, cast(date_part('month', creation_date) as int) as month, count(id) as applicationByMonth
from applications
where cast(date_part('month', creation_date) as int) between 4 and 6
group by cast(date_part('month', creation_date) as int)
union
select '3 trimester' as delta, cast(date_part('month', creation_date) as int) as month, count(id) as applicationByMonth
from applications
where cast(date_part('month', creation_date) as int) between 7 and 9
group by cast(date_part('month', creation_date) as int)
union
select '4 trimester' as delta, cast(date_part('month', creation_date) as int) as month, count(id) as applicationByMonth
from applications
where cast(date_part('month', creation_date) as int) between 10 and 12
group by cast(date_part('month', creation_date) as int)
) as a
inner join
(
select '1 trimester' as delta, cast(date_part('month', creation_date) as int) as month, count(id) as leadByMonth
from m_lead
where cast(date_part('month', creation_date) as int) <= 3
group by cast(date_part('month', creation_date) as int)
union
select '2 trimester' as delta, cast(date_part('month', creation_date) as int) as month, count(id) as leadByMonth
from m_lead
where cast(date_part('month', creation_date) as int) between 4 and 6
group by cast(date_part('month', creation_date) as int)
union
select '3 trimester' as delta, cast(date_part('month', creation_date) as int) as month, count(id) as leadByMonth
from m_lead
where cast(date_part('month', creation_date) as int) between 7 and 9
group by cast(date_part('month', creation_date) as int)
union
select '4 trimester' as delta, cast(date_part('month', creation_date) as int) as month, count(id) as leadByMonth
from m_lead
where cast(date_part('month', creation_date) as int) between 10 and 12
group by cast(date_part('month', creation_date) as int)
) as l
on a.delta = l.delta
group by a.delta
order by 1;
我认为您可以从那里进行调整以满足您的需求。
希望这可以帮助 ;）
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句