我真的不知道如何命名一个主题,所以请原谅我:c我想从数据库中获取一些统计数据。我有3张表,大概是这样的:
Sent messages:
id(send_id)
stamp(timestmap)
Replies:
id
send_id
stamp(timestamp)
Opened:
id
send_id
stamp问题是,在打开的表格中,我保存了打开的每个用户邮件的戳记。因此,如果用户打开我们的电子邮件3次,有3封邮件:
id [1, 2, 3]
send_id [1, 1, 1]
stamp [2017-08-06 15:15:12, 2017-08-07 12:11:12, 2017-08-08 20:15:05] 现在,我想要每天的统计数据,应该是这样的:
sent opened replied date
50 35 10 2017-08-01
20 15 5 2017-08-02
125 80 20 2017-08-03这是我的问题,这是否可以检查前一行中是否已经存在特定的send_id,并跳过计数,因为现在,如果用户在3个不同的日期打开电子邮件,我在那一天的每一天都有+1,但它应该只在他打开电子邮件的第一天加1。
我当前的选择:
select COUNT(DISTINCT s.id) as "sent",
COUNT(DISTINCT CASE WHEN o.send_id is NULL THEN NULL ELSE s.id END) as "opened",
COUNT(DISTINCT CASE WHEN r.send_id is NULL THEN NULL ELSE s.id END) as "replied",
o.stamp::timestamp::date as date
from db_send s
left join db_reply r ON(r.send_id = s.id)
left join db_send_open o ON (o.send_id = s.id)
group by date
order by date所以问题是:我打开的邮件数量要多得多,因为我不知道如何只按send_id对它们进行分组(不是按日期分组,但我也必须按日期分组:ccc,所以存在利益冲突),并选择日期最短的邮件
发布于 2017-08-10 16:16:30
以下是可以解决您的问题的查询。这几乎与Vao提供的功能相同。但是,CTE通常在运行时占用数据库中的内存空间,因为CTE可以在查询脚本中多次使用。建议仅当相同的结果集被重用时才使用CTE。由于这是一个邮件系统,我假设您处理的记录非常多,因此使用CTE可能会降低您的性能。
另外,我假设如果一封邮件从未被打开过,我会将发送时间设为opendate。如果不需要,您可以只删除case语句。
select case when b.opendate is null then date(a.senttime) else b.opendate
end opendate,count(a.id) mail_sent,count(b.send_id) "read",count(distinct
c.send_id) replied
from waggle.sent_kn a
left join
(select send_id,date(min(opentime)) opendate from
waggle.opened_kn
group by send_id)b
on a.id=b.send_id
left join waggle.replied_kn c
on c.send_id = a.id
group by case when b.opendate is null then date(a.senttime) else b.opendate
end;希望这能有所帮助!!:)
发布于 2017-08-08 17:32:01
你可以在CTE中处理开放数据,就像这样:
with db_send_open as (
select min(stamp) stamp, send_id
from db_send_open
group by send_id
)
select COUNT(DISTINCT s.id) as "sent",
count (o.send_id) as "opened",
COUNT(DISTINCT CASE WHEN r.send_id is NULL THEN NULL ELSE s.id END) as "replied",
o.stamp::timestamp::date as date
from db_send s
left join db_reply r ON(r.send_id = s.id)
left join db_send_open o ON (o.send_id = s.id)
group by date
order by datehttps://stackoverflow.com/questions/45564159
复制相似问题