首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >sql group by date给出错误的结果

问sql group by date给出错误的结果
EN

Stack Overflow用户
提问于 2017-08-08 17:11:05
回答 2查看 236关注 0票数 1

我真的不知道如何命名一个主题,所以请原谅我:c我想从数据库中获取一些统计数据。我有3张表,大概是这样的:

代码语言:javascript
复制
Sent messages: 
id(send_id)
stamp(timestmap)

Replies:
id
send_id
stamp(timestamp)

Opened:
id
send_id
stamp

问题是,在打开的表格中,我保存了打开的每个用户邮件的戳记。因此,如果用户打开我们的电子邮件3次,有3封邮件:

代码语言:javascript
复制
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]  

现在,我想要每天的统计数据,应该是这样的:

代码语言:javascript
复制
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。

我当前的选择:

代码语言:javascript
复制
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,所以存在利益冲突),并选择日期最短的邮件

EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2017-08-10 16:16:30

以下是可以解决您的问题的查询。这几乎与Vao提供的功能相同。但是,CTE通常在运行时占用数据库中的内存空间,因为CTE可以在查询脚本中多次使用。建议仅当相同的结果集被重用时才使用CTE。由于这是一个邮件系统,我假设您处理的记录非常多,因此使用CTE可能会降低您的性能。

另外,我假设如果一封邮件从未被打开过,我会将发送时间设为opendate。如果不需要,您可以只删除case语句。

代码语言:javascript
复制
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;

希望这能有所帮助!!:)

票数 1
EN

Stack Overflow用户

发布于 2017-08-08 17:32:01

你可以在CTE中处理开放数据,就像这样:

代码语言:javascript
复制
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 date
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/45564159

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档