时间戳

首页 > 解决方案 > 计算元素并找到最大值

sql - 计算元素并找到最大值

问题描述

我有一张这样的桌子：

+-----+-----+-----+
| uid | aid | tid |
+-----+-----+-----+
| 1   | 6   | 7   |
+-----+-----+-----+
| 2   | 6   | 7   |
+-----+-----+-----+
| 3   | 5   | 7   |
+-----+-----+-----+
| 4   | 5   | 7   |
+-----+-----+-----+
| 5   | 5   | 7   |
+-----+-----+-----+

我期待找到，对于每个 tid，哪个援助有更多的元素。例如，我知道 tid 7 有 2 次aid 6，就像这样。

+

-----+-----+-------+
| tid | aid | count |
+-----+-----+-------+
| 7   | 6   | 2     |
+-----+-----+-------+
| 7   | 5   | 3     |
+-----+-----+-------+

我期望的最终结果是 7 5 3，因为我想要最大计数。

我已经使用两个查询达到了我想要的结果：

CREATE TABLE temp AS
SELECT tid, aid, count(aid) as c
FROM startingtable
GROUP BY tid, aid
ORDER BY tid, aid;

接着

CREATE TABLE result AS
select a.tid, a.aid, a.c
from temp a
inner join
(SELECT tid, max(c) as m
FROM temp
GROUP BY tid) b
on a.tid = b.tid and a.c = b.m
order by tid;

我需要它只使用一个查询就可以正常工作。你会怎么做？

感谢您的时间。

标签： sqlhive

解决方案

您可以尝试将自联接与子查询一起使用。

CREATE TABLE temp AS
SELECT t1.*
FROM (
    SELECT tid, 
           aid,
           count(aid) as cnt       
    FROM startingtable
    GROUP BY tid, aid
) t1 JOIN (
    SELECT tid,
           MAX(cnt) maxcnt
    FROM (
        SELECT tid, 
           aid, 
           count(aid) as cnt
        FROM startingtable
        GROUP BY tid, aid
    ) t2 
    GROUP BY tid
)t2 ON t1.tid = t2.tid and t1.cnt = t2.maxcnt

您可以尝试使用窗口函数将数据插入表中的另一种CTE方法Row_number。

WITH CTE AS (
    SELECT tid, 
       aid, 
       count(aid) as cnt
    FROM startingtable
    GROUP BY tid, aid
)
insert into #temp (tid,aid,cnt) 
select t2.tid,
       t2.aid,
       t2.cnt
from (
    SELECT *,ROW_NUMBER() OVER(PARTITION BY tid order by cnt desc) rn
    FROM CTE 
) t2
where rn = 1

推荐阅读