Получение следующего результата с отметкой времени для привязки к предыдущему результату

Question

Получение следующего результата с отметкой времени для привязки к предыдущему результату

Моя таблица в BigQuery выглядит так

+--------------------+------------+--------+--------+
|     TimeStamp      |  Session   |  Type  | Result |
+--------------------+------------+--------+--------+
| 7/28/2020 16:05:02 | 1595952288 | Select |  53402 |
| 7/28/2020 16:05:16 | 1595952288 | Menu   |      2 |
| 7/28/2020 16:05:25 | 1595952288 | Select |  53405 |
| 7/28/2020 16:05:36 | 1595952288 | Menu   |      1 |
+--------------------+------------+--------+--------+

Я хотел бы получить данные, чтобы они выглядели так, как показано ниже

+--------------------+------------+--------+--------+---------+
|     TimeStamp      |  Session   |  Type  | Result | Confirm |
+--------------------+------------+--------+--------+---------+
| 7/28/2020 16:05:02 | 1595952288 | Select |  53402 |       2 |
| 7/28/2020 16:05:25 | 1595952288 | Select |  53405 |       1 |
+--------------------+------------+--------+--------+---------+

По сути, это данные: человек находится в сеансе в приложении, он вводит 5-значное число, а затем его просят подтвердить его.

Пятизначное число будет под Type = Select.
1 или 2 будут в меню Type = Menu, 1 означает, что это было подтверждено, 2 означает, что это было неправильно, и они хотят повторно ввести 5-значное число.

Я пытаюсь связать следующую строку с 5-значным числом на основе следующей отметки времени и поместить ее в ту же строку, чтобы сообщить об этом.

Вот созданный мной SQL, я не думаю, что он правильный, поскольку он создает дубликаты. Думаю, есть способ получше, но он не очень эффективен. На самом деле, это могло быть просто неправильно.

Select DISTINCT 
table1.Session, 
table1.Result, 
subtable1.Confirm

FROM 
`googleBQ_table` as table1
Left Join (
Select 
Result as Confirm,
Session,
MAX(TimeStamp)


FROM 
`googleBQ_table` 

WHERE Type =  'Menu' and LENGTH(Result) < 2

group by 1,2) as sub_table1 on sub_table1.Session = table1.Session


WHERE
table1.Session = '1595952288' and LENGTH(table1.Result) = 5

Заранее спасибо.

# sql google-bigquery window-functions

GMB05 авг. 2020, в 17:34

Источник

Поделиться в социальных сетях:

Скопировать адрес страницы:

Ответы 2

Смежные вопросы

за 1 против

Предполагая, что строки «select» и «menu» правильно чередуются, вы можете использовать оконные функции:

select timestamp, session, type, result, lead_result confirm
from (
    select 
        t.*, 
        lead(case when type = 'Menu' then result end) 
            over(partition by session order by timestamp) lead_result
    from `googleBQ_table` t
) t
where type = 'Select'

GMB05 авг. 2020, в 14:37

Смежные вопросы

Mikhail Berlyant · Accepted Answer · 2020-08-06T02-23-39.000Z

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

within a Session in an application, they are entering a 5 digit number, then they are asked to confirm it.

на основе вышеизложенного - я предполагаю, что строка подтверждения всегда находится после строки выбора - поэтому ниже это делается

#standardSQL
SELECT * FROM (
  SELECT *, LEAD(result) OVER(confirmation) Confirm 
  FROM `project.dataset.table`
  WINDOW confirmation AS (PARTITION BY session ORDER BY TimeStamp)
) 
WHERE type = 'Select'

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

Row TimeStamp           Session     Type    Result  Confirm  
1   7/28/2020 16:05:02  1595952288  Select  53402   2    
2   7/28/2020 16:05:25  1595952288  Select  53405   1