让我们从我的表格开始(针对我的问题进行了简化((使用MySQL(:
client :
+----------+----------------------+
| idClient | insctiptionDate |
+----------+----------------------+
| 1 | 2017-07-31 14:51:22 |
+----------+----------------------+
| 2 | 2017-07-18 08:25:36 |
+----------+----------------------+
delivery :
+----------+----------------------+
| fkClient | date |
+----------+----------------------+
| 1 | 2017-07-31 14:51:22 |
+----------+----------------------+
| 1 | 2017-08-10 13:30:22 |
+----------+----------------------+
| 2 | 2017-07-18 08:25:36 |
+----------+----------------------+
题词日期列等于每个客户端的首次交付日期。
我需要知道有多少客户在第一次命令后的 28 天内第二次订购。
到目前为止,我有这个:
SELECT
YEARWEEK(tmp.first) AS Week,
COUNT(tmp.id) AS Total
FROM (
SELECT
c.idClient as id,
(SELECT
MIN(d.date)
FROM delivery d
WHERE c.idClient = d.fkClient
) AS first
FROM client c
ORDER BY first ASC
) AS tmp
WHERE (
SELECT
MIN(d.date)
FROM delivery d
WHERE tmp.id = d.fkClient
AND d.date != tmp.first
AND DATEDIFF(d.date, tmp.first) <= 28
) IS NOT NULL
GROUP BY YEARWEEK(first)
ORDER BY YEARWEEK(first);
我首先收到所有客户的第一次交货,然后在接下来的 28 天内计算他们中的多少人第二次订购。
我的问题是:
- 我不确定这是最优雅的解决方案,或者即使这真的有效,所以我愿意在这里接受任何建议 我
- 只能选择一个选择来执行此操作(我正在使用SQL模式的图表以可视化结果(
- 我需要扩展它,以了解有多少客户在他们的第二次交付,他们的第三次交付,...我真的被困在这里了!
感谢您的帮助!
我无法测试它,但我认为你应该做这样的事情:
SELECT IdClient, SUM(NumberCommand)
FROM
( SELECT
SUM(
CASE
WHEN
@previousId = d.fkClient
THEN
CASE
WHEN @prevDate <>d.date
AND DATEDIFF(d.date,@prevDate)<=28
THEN 1
ELSE 0
END
ELSE
1
) AS NumberCommand,
(@previousId := d.fkClient) as IdClient,
(@prevDate := d.date) as DateDelivery
FROM
delivery d,
INNER JOIN (SELECT @prevDate := inscriptionDate, idClient FROM client) b
ON b.idClient = d.fkClient
CROSS JOIN (SELECT @previousId:=0) a
GROUP BY IdClient, DateDelivery
ORDER BY IdClient, d.date) C
GROUP BY IdClient
最后,我决定使用 python 脚本来计算这些值。
无论如何,谢谢!