首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >基于计数结果的类型分类查询

问基于计数结果的类型分类查询
EN

Stack Overflow用户
提问于 2022-10-01 19:57:19
回答 2查看 100关注 0票数 0

我有下面的表,它跟踪id的每分钟条目及其状态:

代码语言:javascript
复制
    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对:

代码语言:javascript
复制
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
;

例如:

代码语言:javascript
复制
    minute id count
    ------ -- ----
    2      B  1002
    3      A  990
    1      C  800
    3      B  798

如何修改查询以获取id的类型?例如,有1002行行。有什么办法可以得到402个固体和600个液体吗?

代码语言:javascript
复制
    minute id count type
    ------ -- ---- -----
    2      B  402  solid
    2      B  600  liquid
    3      A  330  solid
    3      A  660  liquid

我能想到的唯一方法是一个相当复杂的嵌套查询:

代码语言:javascript
复制
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
;

有更简单的方法来进行这个查询吗?目标:

  • 为最常见的1000对,得到类型的细分。
EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2022-10-02 08:30:38

您的查询是

代码语言:javascript
复制
SELECT minute, id, COUNT(*)
FROM state_table
GROUP BY minute, id
ORDER BY COUNT(*) DESC
LIMIT 1000;

在这个过程中,您将失去类型,因为您需要1000个顶级的分钟/id对,所以不能简单地使用group by minute, id, type。

如果这仅涉及“固体”和“液体”类型,则可以应用条件聚合来获得单独的计数:

代码语言:javascript
复制
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位。

代码语言:javascript
复制
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对:

代码语言:javascript
复制
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;
票数 1
EN

Stack Overflow用户

发布于 2022-10-02 09:03:21

我认为最好的方法是,您仍然必须使用两个CTE,而不是嵌套的。

你有三项任务要完成,

  1. CTE将统计记录。

  1. 第二个窗口函数将使用row_number()窗口函数根据减少的顺序或计数分配行号。

。

  1. 最后一次选择将在状态表和第二个cte之间进行连接,使用where子句指定row_nunber <= 1000,并根据ID、状态和分钟进行分组。

以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

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/73921329

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档