在我的mysql数据库中,我有一个表,让我们调用状态。该表格有一个creative_id,每次广告素材的状态发生变化时,我们都会记录一个新行,并将旧行的is_current更改为 0,将新行更改为 1。
我想查询有多少人在状态更改为"已审核"后状态更改超过 90 天。所以下面的查询是我开始的,但问题是,如果某些东西在不到 90 天的时间内发生了变化,但在多次更改中,最终结果大于 90 天怎么办。这是我到目前为止的查询:
SELECT COUNT(*) FROM creative_status_version csv
INNER JOIN creative_status_version csv2
ON csv.creative_id = csv2.creative_id
AND DATEDIFF(day, csv2.last_modified_time, csv.last_modified_time) > 90
WHERE csv.audit_status = 'audited'
AND csv.is_current = 0
AND csv2.audit_status = 'rejected'
AND csv2.is_current = 1
例:
|creative_id| audit_status| is_current| last_modified_time |
|1 |audited | 0 | 1-1-2017 |
|1 |rejecected | 1 | 5-1-2017 |
|2 |audited | 0 | 1-1-2017 |
|2 |pending | 0 | 2-1-2017 |
|2 |rejected | 1 | 5-1-2017 |
|3 |audited | 1 | 1-1-2017 |
|4 |rejected | 1 | 5-1-2017 |
因为审计的唯一结果 ->其他任何事情......ID = 1 的广告素材在 90 天的时间跨度后,结果为:
|Count(*)|
|1 |
我遵循的逻辑是:
- 获取所有审核记录
- 对于相同的广告素材 ID 和已审核记录,获取状态未审核且日期大于审核日期的最短日期
-
如果时差为>90 天,则计数
Select count(*) from (select csv.creative_id, csv.last_modified_time FROM creative_status_version csv INNER JOIN creative_status_version csv2 ON csv.creative_id = csv2.creative_id WHERE csv.audit_status = 'audited' AND csv2.audit_status <> 'audited' AND csv2.last_modified_time>csv.last_modified_time GROUP BY csv.creative_id, csv.last_modified_time HAVING TIMESTAMPDIFF(day, csv.last_modified_time, min(csv2.last_modified_time)) > 90) t
如果您可以有多个经过审核的记录 percreative id,并且只想计算其中最新的记录与最早的非审核记录之间的差异,那么您需要使用一个子查询来代替creative_status_version csv
,该子查询返回状态为审核状态的每个广告素材 ID 的最长时间日期。