Подсчитать количество событий до и после определенного события в SQL?

У меня есть таблица, содержащая дату и события. Есть событие под названием «А». Я хочу узнать, сколько событий произошло до и после события «А» в Sql Bigquery. Например,

User           Date             Events
123          2018-02-13            D
123          2018-02-12            B
123          2018-02-10            C
123          2018-02-11            A
123          2018-02-01            X

Ответ будет примерно таким.

  User       Event    Before   After
  123          A       2        2

Я пробовал много запросов, но это не работает. Любая идея, как решить эту проблему?


sql google-bigquery legacy-sql
person VSR    schedule 13.02.2018    source источник
comment
конечно :o) - этот пример слишком упрощен или у вас действительно есть только одно событие A в этой таблице? Я думаю, что в вашем исходном вопросе я видел две строки с буквой А.   -  person Mikhail Berlyant    schedule 13.02.2018
comment
На самом деле, это из таблицы, где у меня есть несколько пользователей. Каждый пользователь имеет событие A только один раз, но другие события повторяются несколько раз. Я разделил данные для одного пользователя. Таким образом, я подумал, что если проблема будет решена, я смогу реализовать ее для всей таблицы. Фактический ответ будет следующим: событие идентификатора пользователя (т.е. A) до значения после значения.   -  person VSR    schedule 13.02.2018

Ответы (4)


arrow_upward
2
arrow_downward

ниже для BigQuery Standard SQL

#standardSQL
WITH `project.dataset.events` AS (
  SELECT 123 user, '2018-02-13' dt, 'D' event UNION ALL
  SELECT 123, '2018-02-12', 'B' UNION ALL
  SELECT 123, '2018-02-11', 'A' UNION ALL
  SELECT 123, '2018-02-10', 'C' UNION ALL
  SELECT 123, '2018-02-01', 'X' 
)
SELECT user, event, before, after 
FROM (
  SELECT user, event, 
    COUNT(1) OVER(PARTITION BY user ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) before,
    COUNT(1) OVER(PARTITION BY user ORDER BY dt ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING ) after
  FROM `project.dataset.events`
)
WHERE event = 'A'
person Mikhail Berlyant    schedule 13.02.2018
comment
В этом ответе мне пришлось бы вручную объединить все возможные события. Это было бы трудно реализовать для большой таблицы. - person VSR; 13.02.2018
comment
что ты имеешь в виду? это то, что у вас есть в вашем вопросе? :о) - person Mikhail Berlyant; 13.02.2018
comment
просто удалите WHERE event = 'A', и вы получите до и после каждого события для всех пользователей! попробуй и дай мне знать - person Mikhail Berlyant; 13.02.2018
comment
Привет, Михаил, если мне нужно изменить запрос, чтобы проверить, сколько раз определенное событие произошло до A. Но проблема в том, что событие, которое я должен проверить, содержит определенный префикс. Означает, что я должен проверять события, которые начинаются с (XY, затем какое-то имя события). Итак, X.Y.SomeEvent — это события, для которых я должен установить счетчик. Есть идеи? - person VSR; 14.02.2018
comment
Я думаю, вы должны опубликовать это как новый вопрос, чтобы мы не ограничивались форматом комментариев. - person Mikhail Berlyant; 14.02.2018

arrow_upward
0
arrow_downward

Для каждого «A» вы можете получить количество событий до следующего «A», используя row_number() и lead():

select t.*,
       (lead(seqnum) over (order by date) - seqnum - 1) as num_other_events
from (select t.*, row_number() over (order by date) as seqnum
      from t
     ) t
where event = 'A';

Это дает результаты для каждого «А». Учитывая, что у вас есть три «А» в ваших примерах данных и вы хотите только «2», я не уверен, какая логика используется для этого.

person Gordon Linoff    schedule 13.02.2018

arrow_upward
0
arrow_downward

Если вы хотите подсчитать количество событий по мере их появления в таблице перед строкой с событием A, сделать это невозможно, поскольку BigQuery не сохраняет физический порядок строк в таблице.

Если вы хотите подсчитать до и после использования столбца даты, вы можете сделать

WITH
  events AS (
  SELECT
    DATE('2018-02-13') AS event_date,
    "D" AS event
  UNION ALL
  SELECT
    DATE('2018-02-12') AS event_date,
    "B" AS event
  UNION ALL
  SELECT
    DATE('2018-02-10') AS event_date,
    "C" AS event
  UNION ALL
  SELECT
    DATE('2018-02-11') AS event_date,
    "A" AS event
  UNION ALL
  SELECT
    DATE('2018-02-01') AS event_date,
    "X" AS event),
  event_a AS (
  SELECT
    *
  FROM
    events
  WHERE
    event = "A")
SELECT
  ANY_VALUE(event_a.event) AS Event,
  COUNTIF(events.event_date<event_a.event_date) AS Before,
  COUNTIF(events.event_date>event_a.event_date) AS After
FROM
  events,
  event_a
person Daria    schedule 13.02.2018

arrow_upward
0
arrow_downward

Надеюсь, что это ответ на ваш вопрос

Create table #temp(T_date varchar(100),Events varchar(100))

​

insert into #temp values
('2018-02-13','A'),
('2018-02-12','B'),
('2018-02-10','C'),
('2018-02-11','A'),
('2018-02-01','X'),
('2018-02-06','A')

​

select max(rn)-min(rn)
from
(
select *,ROW_NUMBER() over(order by (select 1)) as rn from #temp
)a
where Events='A'
person Suraj Choudhary    schedule 13.02.2018