oracle 使用分组依据选择最频繁值沿着

91zkwejq 于 2023-10-16 发布在 Oracle

关注(0)|答案(1)|浏览(83)

我有以下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，交通-所有交通，位置的总和-最频繁的位置出现在一周
例如

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;

有没有什么有效的方法来做到这一点？

oracle

来源：https://stackoverflow.com/questions/77200042/select-most-frequent-value-along-with-group-by

1条答案

按热度按时间

xxhby3vn1#

您可以使用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

| 活动日期|userId|交通|位置|
| --|--|--|--|
| 2023-09-18 2023-09-18 2023-09-18|用户_1| 150 |一|
| 2023-09-18 2023-09-18 2023-09-18|用户_2| 120 |B|
| 2023-09-18 2023-09-18 2023-09-18|用户_3| 730 |C|
fiddle

UPD：您也可以参考此功能的文档，该文档显示了示例查询，以获得多种模式（如果存在）（您将在原始查询中使用DENSE_RANK代替ROW_NUMBER）。

赞(0）回复(0）举报 2023-10-16

我来回答

oracle 使用分组依据选择最频繁值沿着

1条答案

相关问题

热门标签

最新问答