Как выбрать верхние 75% строк с помощью SQL

У меня есть таблица с 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...

Есть идеи?

sql amazon-athena presto percentile trino

21.02.2023 17:31

Типы данных JavaScript

В JavaScript существует несколько типов данных, включая примитивные типы данных и ссылочные типы данных. Вот краткое объяснение различных типов данных...

Как сделать движок для футбольного матча? (простой вариант)

Футбол. Для многих людей, живущих на земле, эта игра - больше, чем просто спорт. И эти люди всегда мечтают стать футболистом или менеджером. Но, к...

Знайте свои исключения!

В Java исключение - это событие, возникающее во время выполнения программы, которое нарушает нормальный ход выполнения инструкций программы. Когда...

CSS Flex: что должен знать каждый разработчик

CSS Flex: что должен знать каждый разработчик Модуль flexbox, также известный как гибкий модуль разметки box, помогает эффективно проектировать и...

Введение в раздел "Заголовок" в HTML

Говорят, что лучшее о человеке можно увидеть только изнутри, и это относится и к веб-страницам HTML! Причина, по которой некоторые веб-страницы не...

Что такое CommonModule в Angular

Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

Мы можем использовать 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;

Обратите внимание, что для Presto/Trino использование 1.00 вместо 1.0 может быть важным.

— 21.02.2023 19:02

21.02.2023 17:37

Вы можете использовать оконные функции, в частности 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;

Выход:

ID пользователя num_events дом 2 2 1 3 3 1 4 4 1 2 2 2 3 3 2 4 4 2

21.02.2023 17:43