按照分组选择最常见的值

Question

按照分组选择最常见的值

5

我有以下的SQL表格。

eventdate   userid  traffic location    
18.09.2023  user_1  10      A
18.09.2023  user_1  20      A
18.09.2023  user_2  10      B
18.09.2023  user_2  20      B
18.09.2023  user_2  30      B
18.09.2023  user_3  100     A
19.09.2023  user_1  50      B
19.09.2023  user_2  10      B
19.09.2023  user_2  20      B
19.09.2023  user_3  150     C
19.09.2023  user_3  250     C
20.09.2023  user_1  50      A
20.09.2023  user_1  20      A
20.09.2023  user_2  30      B
20.09.2023  user_3  110     C
20.09.2023  user_3  120     C

我想要以下结果： eventdate - 每周的开始日期， userid - 每周的唯一用户ID， traffic - 所有流量的总和， location - 在该周内出现最频繁的位置。

例如：

    eventdate   userid  traffic location
    18.09.2023  user_1  150    A
    18.09.2023  user_2  120    B
    18.09.2023  user_3  730    C

我通过以下查询成功地实现了结果。

SELECT t1.eventdate, t1.userid, t1.traffic, t2.location
  FROM (SELECT TO_CHAR(TRUNC(TO_DATE('2023-09-18', 'yyyy-mm-dd'), 'IW'),
                       'yyyy-mm-dd') AS eventdate,
               tk.userid,
               SUM(tk.traffic) AS traffic
          FROM test_kt tk
         GROUP BY tk.userid) t1
  JOIN (
         WITH cte AS 
        (
         SELECT tk2.userid,
                tk2.location,
                ROW_NUMBER() OVER 
                (PARTITION BY tk2.userid ORDER BY COUNT(tk2.location) DESC) rn
           FROM test_kt tk2
          GROUP BY tk2.userid, tk2.location
        )
        SELECT userid, location 
          FROM cte 
         WHERE rn = 1
       ) t2 
    ON t1.userid = t2.userid;

有没有高效的方法可以这样做？

- Koke Abeke

请澄清您的问题，并提供一些文本格式的示例数据和期望的输出。 - undefined

你每周写作，但是你的所有数据只有几天的范围。你可以添加更多的数据来进行澄清。周指的是星期一到星期日（ISO周），还是其他时间段？ - undefined

1

使用STATS_MODE函数。 - undefined

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- astentx · Accepted Answer

你可以使用STATS_MODE聚合函数来实现相同的效果。

with a(eventdate, userid, traffic, location) as (
  select to_date('18.09.2023', 'dd.mm.yyyy'), 'user_1', 10,  'A' from dual union all
  select to_date('18.09.2023', 'dd.mm.yyyy'), 'user_1', 20,  'A' from dual union all
  select to_date('18.09.2023', 'dd.mm.yyyy'), 'user_2', 10,  'B' from dual union all
  select to_date('18.09.2023', 'dd.mm.yyyy'), 'user_2', 20,  'B' from dual union all
  select to_date('18.09.2023', 'dd.mm.yyyy'), 'user_2', 30,  'B' from dual union all
  select to_date('18.09.2023', 'dd.mm.yyyy'), 'user_3', 100, 'A' from dual union all
  select to_date('19.09.2023', 'dd.mm.yyyy'), 'user_1', 50,  'B' from dual union all
  select to_date('19.09.2023', 'dd.mm.yyyy'), 'user_2', 10,  'B' from dual union all
  select to_date('19.09.2023', 'dd.mm.yyyy'), 'user_2', 20,  'B' from dual union all
  select to_date('19.09.2023', 'dd.mm.yyyy'), 'user_3', 150, 'C' from dual union all
  select to_date('19.09.2023', 'dd.mm.yyyy'), 'user_3', 250, 'C' from dual union all
  select to_date('20.09.2023', 'dd.mm.yyyy'), 'user_1', 50,  'A' from dual union all
  select to_date('20.09.2023', 'dd.mm.yyyy'), 'user_1', 20,  'A' from dual union all
  select to_date('20.09.2023', 'dd.mm.yyyy'), 'user_2', 30,  'B' from dual union all
  select to_date('20.09.2023', 'dd.mm.yyyy'), 'user_3', 110, 'C' from dual union all
  select to_date('20.09.2023', 'dd.mm.yyyy'), 'user_3', 120, 'C' from dual
)
select
  trunc(eventdate, 'iw') as eventdate,
  userid,
  sum(traffic) as traffic,
  stats_mode(location) as location
from a
group by
  trunc(eventdate, 'iw'),
  userid

事件日期	用户ID	流量	位置
2023-09-18	用户_1	150	A
2023-09-18	用户_2	120	B
2023-09-18	用户_3	730	C

fiddle

更新：如果存在多个模式，您还可以查阅此函数的文档，其中显示了获取多个模式的示例查询（在原始查询中，您将使用DENSE_RANK替换ROW_NUMBER）。