Percona MySql 5.6,Linux x64。
我们有一个customers_history
表,该表跟踪随着时间的推移我们的客户的变化。我们要做的是在特定月份内按供应商(lead_source_id)计算变化。
+--------+-------------+----------------+---------------------+--------+
| id | customer_id | lead_source_id | repurchased_date | Rating |
+--------+-------------+----------------+---------------------+--------+
| 422923 | 420450 | 4 | 2014-04-14 09:16:48 | Warm |
| 422924 | 420450 | 4 | 2014-04-14 09:16:48 | Cold |
| 422956 | 420450 | 4 | 2014-04-14 09:16:49 | Hot |
| 422933 | 420451 | 37 | 2014-04-14 09:18:41 | Hot |
| 422938 | 420452 | 1 | 2014-04-10 20:50:30 | Hot |
| 422984 | 420452 | 1 | 2014-04-12 20:50:30 | Hot |
| 422940 | 420453 | 47 | 2014-04-14 09:20:27 | Hot |
+--------+-------------+----------------+---------------------+--------+
给定上述示例,我们想要的是此报告,该报告按供应商(lead_source_id)报告回购。符合条件的是重新购买日期更新。仅更改等级不算在内。
+----------------+-------+
| lead_source_id | count |
+----------------+-------+
| 4 | 2 |
| 37 | 1 |
| 1 | 2 |
| 47 | 1 |
+----------------+-------+
我们最初尝试了此方法:
SELECT count(DISTINCT(ch.repurchased_date)) FROM customers_history ch WHERE Year(ch.repurchased_date) = 2014 AND Month(ch.repurchased_date) = 4 AND ch.lead_source_id IS NOT NULL;
但是,计数与将where子句更改为时返回的行数不同SELECT DISTINCT(ch.created_at)), lead_source_id
。
无论如何,我们在一个腌制罐中试图解决这个问题。多谢您提供任何帮助或指示。
抓取。抱歉,谢谢您到目前为止的回答,但是我完全放弃了为什么这个问题如此棘手的问题。这实际上是一个历史记录表,它记录了多个列中的更改。我编辑了原始问题。
请注意,评分更改时repurchased_date如何保持不变。我们想从计数中排除行422923,但要计数行422924和422956。
您的查询看起来非常接近。我在想,所需要做的就是添加一个GROUP BY
子句。
在COUNT(DISTINCT foo)
将有效地“崩溃”相同的值,使得计仅得到由1对于每个递增:组:相同的日期值。
根据样本数据和所需的结果集,这应该可以工作:
SELECT ch.lead_source_id
, COUNT(DISTINCT ch.repurchased_date)
FROM customers_history ch
WHERE ch.repurchased_date >= '2014-04-01'
AND ch.repurchased_date < '2014-04-01' + INTERVAL 1 MONTH
AND ch.lead_source_id IS NOT NULL
GROUP
BY ch.lead_source_id
在示例数据中,customer_id
和lead_source_id
之间相互关联。(可能是由于样本量太小...)
(有关索引,索引范围扫描和使用覆盖索引的GROUP BY优化的其他注释,请参见下面的注释。)
问题更新之前的答案
这是返回指定结果的一种方法,除了排序,我无法辨别模式。
SELECT ch.lead_source_id
, COUNT(1) AS count_
FROM customers_history ch
WHERE ch.cust_updated_at >= '2014-04-01'
AND ch.cust_updated_at < '2014-04-01' + INTERVAL 1 MONTH
AND ch.lead_source_id IS NOT NULL
GROUP BY ch.lead_source_id
ORDER BY ?
更新
如果您希望“计数”也要通过cust_updated_at
,请在中添加该列GROUP BY
。例如,如果对于此样本数据:
+--------+-------------+----------------+---------------------+
| id | customer_id | lead_source_id | cust_updated_at |
+--------+-------------+----------------+---------------------+
| 422924 | 420450 | 4 | 2014-04-14 09:16:48 |
| 422956 | 420450 | 4 | 2014-04-14 09:16:48 |
| ?????? | 420450 | 4 | 2014-04-15 22:22:22 |
+--------+-------------+----------------+---------------------+
您要返回:
+----------------+-------+
| lead_source_id | count |
+----------------+-------+
| 4 | 2 |
| 4 | 1 |
+----------------+-------+
然后,将该cust_updated_at
列添加到GROUP BY
子句中,例如
SELECT ch.lead_source_id
, COUNT(1) AS count_
FROM customers_history ch
WHERE ch.cust_updated_at >= '2014-04-01'
AND ch.cust_updated_at < '2014-04-01' + INTERVAL 1 MONTH
AND ch.lead_source_id IS NOT NULL
GROUP
BY ch.lead_source_id
, ch.cust_updated_at
笔记:
(如果我们省略该ORDER BY
子句,并且该GROUP BY
子句隐式地ORDER BY
在相同的一组表达式上应用。我们只需要指定一个ORDER BY
子句即可获得不同的顺序。)
另外,在谓词的函数中包装日期列会阻止MySQL通过使用索引范围扫描来满足谓词。我们通常喜欢在谓词中包含“裸日期列”,并在常量方面进行所需的任何处理。(将date列包装在函数中,例如,YEAR()
强制MySQL对表中的每一行(或未被其他谓词过滤掉的每一行)评估该函数。)
为了获得最佳性能,此查询的合适覆盖范围索引应为:
... ON customer_history (lead_source_id, created_at)
MySQL可以完全通过索引满足查询;说明输出将显示“使用索引”。如果我们取消ORDER BY子句,MySQL还将避免“使用文件排序”操作。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句