我有下面的表,它跟踪id的每分钟条目及其状态:
minute id type
------ -- ----
1 A solid
1 A solid
1 A solid
1 A liquid
1 B solid
1 B solid
1 B liquid
.... 1000+ rows ...
1 ZZX liquid
1 ZZZ liquid
2 A solid
2 A solid
2 A liquid通过下面的查询,我可以根据事件得到前1000对:
With TempIds AS (
SELECT
state_table.minute as minute,
state_table.id as id,
COUNT(*)
FROM
state_table
GROUP BY 1,2
) SELECT
TempId.minute,
TempId.id,
TempId.count
FROM
TempIds
ORDER BY 3 DESC
LIMIT 1000
;例如:
minute id count
------ -- ----
2 B 1002
3 A 990
1 C 800
3 B 798如何修改查询以获取id的类型?例如,有1002行行。有什么办法可以得到402个固体和600个液体吗?
minute id count type
------ -- ---- -----
2 B 402 solid
2 B 600 liquid
3 A 330 solid
3 A 660 liquid我能想到的唯一方法是一个相当复杂的嵌套查询:
With TempTop AS (
With TempIds AS (
SELECT
state_table.minute as minute,
state_table.id as id,
COUNT(*)
FROM
state_table
GROUP BY 1,2
) SELECT
TempId.minute as minute,
TempId.id as id,
TempId.count
FROM
TempIds
ORDER BY 3 DESC
LIMIT 1000
)
) SELECT
state_table.minute,
state_table.id,
state_table.type,
COUNT(*)
FROM
state_table, TempTop
WHERE
state_table.minute = TempTop.minute
AND state_table.id = TempTop.id
;有更简单的方法来进行这个查询吗?目标:
发布于 2022-10-02 08:30:38
您的查询是
SELECT minute, id, COUNT(*)
FROM state_table
GROUP BY minute, id
ORDER BY COUNT(*) DESC
LIMIT 1000;在这个过程中,您将失去类型,因为您需要1000个顶级的分钟/id对,所以不能简单地使用group by minute, id, type。
如果这仅涉及“固体”和“液体”类型,则可以应用条件聚合来获得单独的计数:
SELECT
minute, id,
COUNT(*) AS total,
SUM(type = 'solid') AS solid,
SUM(type = 'liquid') AS liquid
FROM state_table
GROUP BY minute, id
ORDER BY COUNT(*) DESC
LIMIT 1000;总结布尔表达式在MySQL中是有效的,因为true =1,false =0。
顺便说一下,上述查询的问题是联系。如果有两对,具有相同的1000计数,你选择一个,而不是只显示999或1001对,然后,为了处理这两个配对相同。因此,为了正确处理关系,我可能会使用DENSE_RANK重写查询。
对于在编写查询时类型未知的更复杂的情况,您需要行而不是列,就像在请求中已经显示的那样。在这种情况下,您确实需要首先使用group by minute, id, type。获得总计数的最简单方法是使用SUM OVER。然后用DENSE_RANK对这些对进行排序,并保持前1000位。
SELECT minute, id, type, cnt
FROM
(
SELECT
minute, id, type, cnt,
DENSE_RANK() OVER (ORDER BY total DESC) AS rnk
FROM
(
SELECT
minute, id, type,
COUNT(*) AS cnt,
SUM(COUNT(*)) OVER(PARTITION BY minute, id) AS total
FROM state_table
GROUP BY minute, id, type
) counted
) ranked
WHERE rnk <= 1000
ORDER BY rnk, minute, id, type;这可以使你超过1000分钟/id对在领带的情况下。您可以使用RANK而不是DENSE_RANK来减少这种情况。如果这两种方法仍然没有得到所需的数字,则可能必须在子查询中分别计算分钟/id对:
select minute, id, type, COUNT(*)
from state_table
WHERE (minute, id) IN
(
SELECT minute, id
FROM state_table
GROUP BY minute, id
ORDER BY COUNT(*) DESC
LIMIT 1000
)
GROUP BY minute, id, type
ORDER BY
SUM(COUNT(*)) OVER (PARTITION BY minute, id) DESC,
minute, id, type;发布于 2022-10-02 09:03:21
我认为最好的方法是,您仍然必须使用两个CTE,而不是嵌套的。
你有三项任务要完成,
。
以TempIdsCount为(选择state_table.minute为分钟,以state_table.id为id,从state_table组中计数(*) rw_cnt by 1,2) TempIdsRowNum为(选择分钟,id,Row_number() over (由rw_cnt desc命令) rw_num从TempIdsCount )选择st.minute、st.id、st.type、Count(*) cnt从state_table st联合TempIdsRowNum trn on st.minute = trn.minute和st.minute =TempIdsRowNum,其中#on 20# 1000组按,st.id,st.type
https://stackoverflow.com/questions/73921329
复制相似问题