BigQuery: извлекает строки, уникальные для двух столбцов, в противном случае - строку с самым большим третьим столбцом.

У меня есть таблица BigQuery my_table, которая выглядит следующим образом:

+---------+---------+-------+------------------+----------+--------+-----+--------+
| poll_id | user_id | count |    timestamp     | timezone | answer | age | gender |
+---------+---------+-------+------------------+----------+--------+-----+--------+
|       1 |       1 |     5 | 2019-08-06 11:00 |        1 | no     |  25 | male   |
|       1 |       1 |    10 | 2019-08-06 10:00 |        1 | no     |  25 | male   |
|       1 |       1 |    10 | 2019-08-06 10:30 |        1 | yes    |  25 | male   |
|       1 |       2 |    10 | 2019-08-06 11:00 |        1 | no     |  35 | male   |
|       1 |       2 |    20 | 2019-08-06 11:00 |        1 | no     |  35 | male   |
|       1 |       2 |    35 | 2019-08-06 11:00 |        1 | NULL   |  35 | male   |
|       2 |       1 |    10 | 2019-08-06 10:35 |        1 | no     |  25 | male   |
|       3 |       1 |    10 | 2019-08-06 10:35 |        1 | NULL   |  25 | male   |
+---------+---------+-------+------------------+----------+--------+-----+--------+

Я хочу получить строки, которые удовлетворяют следующим требованиям:

  • Если строка содержит уникальную комбинацию poll_id и user_id, включите строку, если она имеет ненулевое значение в answer
  • If the row does not have a unique combination of poll_id and user_id:
    • Include the row with the largest count that is not NULL in the answer column
      • If there are two rows with the same count (and non-NULL answer), include the row with the largest timestamp

Я также хочу иметь возможность ограничить поиск определенной датой и часовым поясом, например датой 2019-08-06 и часовым поясом 1, и я не хочу извлекать строки со значением NULL в user_id .

До сих пор я пробовал следующий стандартный оператор SQL:

  SELECT
    t1.poll_id,
    t1.user_id,
    t1.count,
    t1.timestamp,
    t1.timezone,
    t1.answer,
    t1.age,
    t1.gender,
  FROM
    `my_table` t1
  LEFT JOIN
    `my_table` t2
  ON
    t1.poll_id = t2.poll_id
    AND t1.user_id = t2.user_id
    AND t1.count < t2.count
    AND t2.answer IS NOT NULL
    AND DATE(t2.timestamp, "+1:00") = "2019-08-06"
  WHERE
    t1.user_id IS NOT NULL
    AND t1.answer IS NOT NULL
    AND DATE(t1.timestamp, "+1:00") = "2019-08-06"
    AND t1.timezone = 1   
    AND t2.count IS NULL

Ожидаемый результат для показанной таблицы:

+---------+---------+-------+------------------+----------+--------+-----+--------+
| poll_id | user_id | count |    timestamp     | timezone | answer | age | gender |
+---------+---------+-------+------------------+----------+--------+-----+--------+
|       1 |       1 |    10 | 2019-08-06 10:30 |        1 | yes    |  25 | male   | // count = 10 and largest timestamp
|       1 |       2 |    20 | 2019-08-06 11:00 |        1 | no     |  35 | male   | // count = 20 (the 35 row had NULL in 'answer')
|       2 |       1 |    10 | 2019-08-06 10:35 |        1 | no     |  25 | male   | // unique 'poll_id', 'user_id' combination
+---------+---------+-------+------------------+----------+--------+-----+--------+

Однако, похоже, есть две проблемы:

  1. Если имеется несколько строк с одинаковым (самым большим) значением count, извлекаются все эти строки. Это означает, что в этом примере извлекаются строки 2 и 3.
  2. Если для комбинации poll_id, user_id есть ровно две строки, ни одна из них не извлекается, даже если они имеют разные count значения.

По крайней мере, так кажется. Мне сложно отследить проблему и, конечно же, выяснить правильный запрос.

Любая помощь будет оценена по достоинству.


sql google-bigquery bigquery-standard-sql
person Robin    schedule 08.08.2019    source источник

Ответы (2)


arrow_upward
2
arrow_downward

Ниже приведен стандартный SQL BigQuery.

#standardSQL
SELECT * EXCEPT(pos) 
FROM (
  SELECT *, 
    ROW_NUMBER() OVER(PARTITION BY poll_id, user_id ORDER BY count DESC, timestamp DESC) AS pos
  FROM `project.dataset.table`
  WHERE NOT answer IS NULL
  AND NOT user_id IS NULL
  AND timezone = 1
  AND SUBSTR(timestamp, 1, 10) = '2019-08-06'
)
WHERE pos = 1

Если обратиться к образцу данных из вашего вопроса - результат

Row poll_id user_id count   timestamp           timezone    answer  age gender   
1   1       1       10      2019-08-06 10:30    1           yes     25  male     
2   1       2       20      2019-08-06 11:00    1           no      35  male     
3   2       1       10      2019-08-06 10:35    1           no      25  male
person Mikhail Berlyant    schedule 08.08.2019

arrow_upward
2
arrow_downward

Для этого типа запроса обычно подходит row_number(). Я думаю, это соответствует тому, что вы описали:

select t.*
from (select t.*,
             row_number() over (partition by poll_id, user_id order by count desc, timestamp desc) as seqnum
      from my_table t
      where answer is not nll
     ) t
where seqnum = 1;
person Gordon Linoff    schedule 08.08.2019