mysql - SQL:来自具有不同日期格式的不同表的聚合

Question

使用以下 sql 表:

表:财政

DateID   | date                  | fiscal_year | fiscal_week 
20170101   2017-01-01 00:00:00.0   2017          2017 WK 01
20170102   2017-01-02 00:00:00.0   2017          2017 WK 01

表:email_info

email_id | email_name    | email_subjectline
123        New_Year_2017   Welcome the new year!
345        Reminder        Don't forget 

表:sent_info

email_id | sent_date
123      | 1/1/2017 8:58:39 PM
345      | 1/2/2017 6:33:39 AM

表:click_info

recipient | email_id | click_date
XYZ         123         1/7/2017 4:25:27 PM
ABC         123         1/5/2017 3:13:56 AM
CDF         345         1/6/2017 2:20:16 AM
ABC         345         1/14/2017 3:33:25 AM

显然每个表中有很多行。

电子邮件表之间的连接很简单。

SELECT * 
FROM email_info
JOIN sent_info
ON sent_info.email_id = email_info.email_id
JOIN click_info
ON click_info.email_id = email_info.email_id

我正在努力解决以下问题:

• 如何将所有日期设置为相同的格式？ (我不需要时间，只有一天)
• 如何加入财务表以便我可以按财务周进行过滤
• 如何计算一封电子邮件在发送日期后 7 天内的所有点击次数(这不能按日期硬编码，但必须是动态的)

这是我正在寻找的输出(按财政周 = 2017 WK 01 过滤):

email_id | email_name    | sent_date |  fiscal_week | Clicks
123        New_year_2017   1/1/2017     2017 WK 01     2
345        Reminder        1/2/2017     2017 WK 01     1

*请注意，click_info 表示例中的最后一次点击未被计算在内，因为它已超出发送日期后的 7 天。

** DateID 为整数，sent_date 和 click_date 为字符串/varchar

Answer 1

假设dateId是varchar其他都是datetime应该是

select a.email_id, a.email_name, date(b.sent_date), c.fiscal_week, count(d.click_date)
from email_info a 
inner join fiscal c on  str_to_date(c.dateID, '%Y%m%d') = date(b.sent_date)
inner join  sent_info b on b.email_id = c.email_id
inner join click_info d on d.email_id = b.email_id 
    and date(d.click_date) between date(b.sent_date) and DATEADD(week,1,date(b.sent_date))
group by  a.email_id, a.email_name, date(b.sent_date), c.fiscal_week

PS 如果您有其他格式可以正确转换并更改单件，请确定查询结构已定义