MySQL计数/跟踪条纹或连续日期 [英] MySQL count / track streaks or consecutive dates
问题描述
+ ----- ++ ------------ + ------------ + ----- ---------------- +
| id | Seller_id | prod_id |日期|
+ ----- + ------------ + -------------------------- -------- +
| 1 | 283 | 4243 | 2016-10-10 23:55:01 |
| 2 | 287 | 4243 | 2016-10-10 02:01:06 |
| 3 | 283 | 4243 | 2016-10-11 23:55:06 |
| 4 | 311 | 4243 | 2016-10-11 23:55:07 |
| 5 | 283 | 4243 | 2016-10-12 23:55:07 |
| 6 | 283 | 4243 | 2016-10-13 23:55:07 |
| 7 | 311 | 4243 | 2016-10-13 23:55:07 |
| 8 | 287 | 4243 | 2016-10-14 23:57:06 |
| 9 | 311 | 4243 | 2016-10-14 23:57:06 |
| 10 | 311 | 4243 | 2016-10-15 23:57:06 |
+ ----- + ------------ + ------------ + ------------- -------- +
从上表中，我将如何使用一个MySQL查询吗？
+ ------------ + -------- -+ ---------------- + --------------- +
| Seller_id | prod_id |连胜|开始连胜|
+ ----- + ------------ + -------------------- + ----- ---------- +
| 283 | 4243 | 4 | 2016-10-10 |
| 287 | 4243 | 1 | 2016-10-10 |
| 311 | 4243 | 1 | 2016-10-11 |
| 311 | 4243 | 3 | 2016-10-13 |
| 287 | 4243 | 1 | 2016-10-14 |
+ ------------ + --------- + ---------------- + ----- ---------- |
所以基本上我需要为每个销售商品（prod_id ）。
我将这个示例限制为1个prod_id，并且范围只有几天，但是卖家确实销售了不止一种产品（prod_id）
选择
Seller_id
，prod_id
，COUNT（*）as StreakInDays
，MIN（DateCol）作为BeginStreak
从
（
选择
Seller_id
，prod_id
，DATE（DateCol）作为DateCol
，（@ rn：= if（（@ seller = Seller_id）AND（@ prod = prod_id），@rn + 1，
if（（@ seller：= Seller_id）AND（@prod：= prod_id ），1，1）
）
）作为RowNumber
FROM
交易t
交叉联接（SELECT @seller：= 0，@prod：= 0，@ rn：= 0）var
ORDER BY
Seller_id
，prod_id
，DATE（DateCol）
）t
GROUP BY
Seller_ id
，prod_id
，DATE_SUB（DateCol，INTERVAL RowNumber Day）
ORDER BY
prod_id
，DATE_SUB（DateCol，INTERVAL RowNumber Day）
，seller_id
生成一个由Seller_id和prod_id分区的分区行号。然后使用Date-RownNumber作为分组，您可以通过简单的汇总获得答案。
SQL Fiddle显示它适用于多种产品，销售商等。 a href = http://sqlfiddle.com/#!9/0a0c44/8/0 rel = nofollow noreferrer> http://sqlfiddle.com/#!9/0a0c44/8/0
请注意，如果同一位卖家在同一天可能有一项商品的交易超过1笔，则您需要将Transact替换为在生成这样的行号之前，先将DISTINCT Seller_id，prod_id，DATE（日期）设置为 b，prod_id
，COUNT（*）作为StreakInDays
，MIN（DateCol）作为BeginStreak
FROM
（
选择
Seller_id
， prod_id
，DateCol
，（@ rn：= if（（@ seller = Seller_id）AND（@ prod = prod_id），@ rn + 1，
if（（@ seller：= Seller_id ）AND（@prod：= prod_id），1，1）
）
）为RowNumber
FROM
（选择DISTINCT Seller_id，prod_id，DATE（DateCol）作为DateCol
FROM
交易
）t
交叉联接（SELECT @seller： = 0，@prod：= 0，@rn：= 0）var
ORDER BY
Seller_id
，prod_id
，DateCol
）t
GROUP由
Seller_id
，prod_id
，DATE_SUB（DateCol，INTERVAL行号日期）
排序由
prod_id
，DATE_SUB（DateCol，INTERVAL行号日期）
，seller_id
http://sqlfiddle.com/#!9/0a0c44/11
+-----+------------+------------+---------------------+
| id | seller_id | prod_id | date |
+-----+------------+----------------------------------+
| 1 | 283 | 4243 | 2016-10-10 23:55:01 |
| 2 | 287 | 4243 | 2016-10-10 02:01:06 |
| 3 | 283 | 4243 | 2016-10-11 23:55:06 |
| 4 | 311 | 4243 | 2016-10-11 23:55:07 |
| 5 | 283 | 4243 | 2016-10-12 23:55:07 |
| 6 | 283 | 4243 | 2016-10-13 23:55:07 |
| 7 | 311 | 4243 | 2016-10-13 23:55:07 |
| 8 | 287 | 4243 | 2016-10-14 23:57:06 |
| 9 | 311 | 4243 | 2016-10-14 23:57:06 |
| 10 | 311 | 4243 | 2016-10-15 23:57:06 |
+-----+------------+------------+---------------------+
From the table above how would I extract the following information using an MySQL query?
+------------+---------+----------------+---------------+
| seller_id | prod_id | streak in days | begin streak |
+-----+------------+--------------------+---------------+
| 283 | 4243 | 4 | 2016-10-10 |
| 287 | 4243 | 1 | 2016-10-10 |
| 311 | 4243 | 1 | 2016-10-11 |
| 311 | 4243 | 3 | 2016-10-13 |
| 287 | 4243 | 1 | 2016-10-14 |
+------------+---------+----------------+---------------|
So basically I need to identify each block of consecutive dates for each seller (seller_id) selling products (prod_id).
I limited this example to 1 prod_id and only a range of a few days, but sellers do sell more than 1 product (prod_id)
SELECT
seller_id
,prod_id
,COUNT(*) as StreakInDays
,MIN(DateCol) as BeginStreak
FROM
(
SELECT
seller_id
,prod_id
,DATE(DateCol) as DateCol
,(@rn:= if((@seller = seller_id) AND (@prod = prod_id), @rn + 1,
if((@seller:= seller_id) AND (@prod:= prod_id), 1, 1)
)
) as RowNumber
FROM
Transact t
CROSS JOIN (SELECT @seller:=0, @prod:=0, @rn:=0) var
ORDER BY
seller_id
,prod_id
,DATE(DateCol)
) t
GROUP BY
seller_id
,prod_id
,DATE_SUB(DateCol, INTERVAL RowNumber Day)
ORDER BY
prod_id
,DATE_SUB(DateCol, INTERVAL RowNumber Day)
,seller_id
Generate a partitioned row number partitioned by seller_id and prod_id. Then use the Date - RownNumber as a grouping and you can get to your answer by simple aggregation.
SQL Fiddle to show you it works for multiple products, sellers etc. http://sqlfiddle.com/#!9/0a0c44/8/0
Note if it is possible that the same seller can have more than 1 transaction for a product on the same day then you will need to replace the Transact with a derived table of DISTINCT seller_id, prod_id, DATE(date) before generating the row number like this:
SELECT
seller_id
,prod_id
,COUNT(*) as StreakInDays
,MIN(DateCol) as BeginStreak
FROM
(
SELECT
seller_id
,prod_id
,DateCol
,(@rn:= if((@seller = seller_id) AND (@prod = prod_id), @rn + 1,
if((@seller:= seller_id) AND (@prod:= prod_id), 1, 1)
)
) as RowNumber
FROM
(SELECT DISTINCT seller_id, prod_id, DATE(DateCol) as DateCol
FROM
Transact
)t
CROSS JOIN (SELECT @seller:=0, @prod:=0, @rn:=0) var
ORDER BY
seller_id
,prod_id
,DateCol
) t
GROUP BY
seller_id
,prod_id
,DATE_SUB(DateCol, INTERVAL RowNumber Day)
ORDER BY
prod_id
,DATE_SUB(DateCol, INTERVAL RowNumber Day)
,seller_id
http://sqlfiddle.com/#!9/0a0c44/11
这篇关于MySQL计数/跟踪条纹或连续日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
