对行中的值也在前一行中的行进行计数

Question

我想得到一个计数，其中一行中的值的内容也在前一行中。

Row | Item1 | Item2 | Item 3 |
1   | Dog   | Cat   | Rat
2   | Bird  | Cat   | Horse
3   | Horse | Dog   | Rat
4   | Bird  | Cat   | Horse
5   | Horse | Bird  | Cat

第2行将增加Cat的计数，因为Cat在第1行和第2行中

第3行将增加马的数量，因为马也在第2行中

第4行将增加马的数量，因为马也在第3行中

第5行将增加"马和猫"的计数，因为这两者都出现在第4行。

最多可以有100个商品或SKU，我可以对任何或所有字段进行索引。在任何给定的时间，可能有1000到2000行。

除了"SELECT*FROM table where"之外，我甚至无法思考从哪里开始这个查询

Answer 1

首先，使用SKU的所有可用唯一值创建表格：

CREATE TABLE results(
id    VARCHAR(255) NOT NULL PRIMARY KEY
);
-- All fields should be listed here one-by-one.
INSERT IGNORE INTO results (select Item1 from example);
INSERT IGNORE INTO results (select Item2 from example);
INSERT IGNORE INTO results (select Item3 from example);

前一行可以通过左键将主表与自身重新连接来获得，即LEFT JOIN example AS previous ON previous.id + 1 = example.id。

之后，我们必须检查每个唯一的结果是否存在于当前行和前一行的示例表中，最后得到：

SELECT
r.*,
SUM(
CASE WHEN r.id IN (
prv.Item1, prv.Item2, prv.Item3  -- All fields should be listed here.
) THEN 1 ELSE 0 END
) AS total
FROM
results AS r
LEFT JOIN
example AS cur ON r.id IN (
cur.Item1, cur.Item2, cur.Item3    -- All fields should be listed here.
)
LEFT JOIN
example AS prv ON prv.id + 1 = cur.id
GROUP BY
r.id
ORDER BY
cur.id
;

参见工作示例http://www.sqlfiddle.com/#！9/7 ebd85/1/0

Answer 2

这可以通过窗口函数(在MySQL 8.0中可用(来完成

一个选项是取消预览结果集，然后使用lag()检查上一条记录。假设ids总是增加1，则可以执行以下操作：

select
item,
sum(case when id = lag_id + 1 then 1 else 0 end) cnt_consecutive
from (
select
t.*,
lag(id) over(partition by item order by id) lag_id
from (
select id, item1 item from mytable
union all select id, item2 from mytable
union all select id, item3 from mytable
) t
) t
group by item
order by item

---

如果没有递增列，可以使用dense_rank():生成一个

select
item,
sum(case when new_id = lag_new_id + 1 then 1 else 0 end) cnt_consecutive
from (
select 
t.*,
lag(new_id) over(partition by item order by new_id) lag_new_id
from (
select
t.*,        
dense_rank() over(order by id) new_id
from (
select id, item1 item from mytable
union all select id, item2 from mytable
union all select id, item3 from mytable
) t
) t
) t
group by item
order by item

---

在这个DB Fiddle中，两个查询都返回：

项目|cnt_secutive：----|----------------：鸟|1类别|2狗|0马|3大鼠|0

Answer 3

我看到@frost-nzcr4的建议非常好，我正在做我自己的版本，与昨天的版本非常相似。然而，我所做的方法有点不同，因为我没有专门创建一个表来存储唯一值。相反，我做了类似@GMBUNION的子查询，结果是这样的：

SELECT B.row, A.allitem,
SUM(CASE WHEN A.allitem IN (C.Item1, C.Item2, C.Item3) THEN 1 
ELSE 0 END) AS total
FROM
-- this sub-query will be dynamic and UNION will eliminate any duplicate
(SELECT item1 AS allitem FROM mytable UNION
SELECT item2 FROM mytable UNION
SELECT item3 FROM mytable) AS A
LEFT JOIN mytable AS B ON A.allitem IN (B.Item1, B.Item2, B.Item3)
LEFT JOIN mytable AS C ON C.row + 1 = B.row
GROUP BY  A.allitem
ORDER BY  B.row;

Fiddle here：https://www.db-fiddle.com/f/bUUEsaeyPpAMfR2bK1VpBb/2

正如您所看到的，这与frost的建议完全相似，只是做了一些小的修改。在子查询中，只要插入了新值，allitem值就会更新，因此您不需要一直将新的唯一数据插入到单独的表中。

此外，除非删除sql_mode，否则此查询在MySQL v5.7上通常会出现this is incompatible with sql_mode=only_full_group_by错误。