У меня есть таблица с 3 столбцами: user
(строка), home
(строка), num_events
(целое число), например:
user1,A,4
user2,B,5
user3,A,2
user4,C,12
Определенный дом может «содержать» много пользователей, но каждый пользователь принадлежит только к одному дому.
Мне нужно отфильтровать всех пользователей первого квартиля с меньшим числом_событий для каждого дома. Чтобы сделать это, мой план состоит в том, чтобы перечислить всех пользователей для каждого дома, затем упорядочить по убыванию num_events и оставить только первые 75%, а затем подсчитать пользователей для каждого дома.
Мои попытки похожи на это:
WITH t AS (
SELECT home, user, num_events
FROM table
GROUP BY home_cusec, user, num_events
ORDER BY home_cusec, num_events DESC
)
SELECT home, COUNT(distinct user) FROM t
WHERE num_events > APPROX_PERCENTILE(num_events, 0.25)
GROUP BY home
ORDER BY home
Но ни одна из моих попыток не увенчалась успехом. Обратите внимание, что я использую AWS Athena, и функция TOP не зарегистрирована, поэтому я не могу использовать
SELECT TOP 75 PERCENT * FROM t
я пробовал с
LIMIT (SELECT ROUND( COUNT(*)*0.75 FROM t)
Но и с этим запрос недействителен в AWS Athena...
Есть идеи?
Мы можем использовать ROW_NUMBER()
и COUNT()
здесь:
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY num_events DESC) rn,
COUNT(*) OVER () AS cnt
FROM yourTable
)
SELECT user, home, num_events
FROM cte
WHERE 1.0*rn / cnt <= 0.75;
Вы можете использовать оконные функции, в частности row_number
и count
:
WITH with_row_nums AS (
SELECT *,
row_number() over (partition by home order by num_events) row_num,
count(*) over (partition by home) AS cnt
FROM table_with_3_col
)
SELECT user, home, num_events
FROM with_row_nums
WHERE 1.00 * row_num / cnt > 0.25; -- 1.00 is important here or use cast(row_num as decimal(18,2)) / cnt > 0.25
Например:
-- generate data 2 homes of 4 users each
with data as (
SELECT user as user_id, user as num_events, home
from unnest(sequence(1, 4)) as u(user),
unnest(sequence(1,2)) as h(home)
),
-- query parts
with_row_nums as(
select * ,
row_number() over (partition by home order by num_events) row_num,
count() over (partition by home) cnt
from data
)
select user_id, num_events, home
from with_row_nums
WHERE cast(row_num as decimal(18,2)) / cnt > 0.25
order by home, user_id;
Выход:
Обратите внимание, что для Presto/Trino использование
1.00
вместо1.0
может быть важным.