我有下面的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;
有什么有效的方法可以做到这一点吗?