Używam PostgreSQL 8.0 i mam tabelę z user_id, timestamp i event_id.

Jak mogę zwrócić wiersze (lub wiersz) po czwartym wystąpieniu event_id = someID na użytkownika?

|---------------------|--------------------|------------------|
|      user_id        |     timestamp      |     event_id     |
|---------------------|--------------------|------------------|
|          1          |  2020-04-02 12:00  |        11        |
|---------------------|--------------------|------------------|
|          2          |  2020-04-02 13:00  |        11        |
|---------------------|--------------------|------------------|
|          2          |  2020-04-02 14:00  |        99        |
|---------------------|--------------------|------------------|
|          2          |  2020-04-02 15:00  |        11        |
|---------------------|--------------------|------------------|
|          2          |  2020-04-02 16:00  |        11        |
|---------------------|--------------------|------------------|
|          2          |  2020-04-02 17:00  |        11        |
|---------------------|--------------------|------------------|
|          2          |  2020-04-02 17:00  |        11        |
|---------------------|--------------------|------------------|

To znaczy, jeśli event_id = 11, chciałbym tylko ostatniego wiersza w powyższej tabeli.

1
Indy 3 kwiecień 2020, 00:40

3 odpowiedzi

Najlepsza odpowiedź

Przepraszam, że pytam o tak starą wersję Postgres, oto odpowiedź, która zadziałała:

WITH EventOrdered AS(
  SELECT 
    EventTypeId
    , UserId
    , Timestamp
    , ROW_NUMBER() OVER (PARTITION BY EventTypeId, UserId ORDER BY Timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ROW_NO
  FROM Event),
FourthEvent AS (
  SELECT DISTINCT
    UserID
  , FIRST_VALUE(TimeStamp) OVER (PARTITION BY UserId ORDER BY Timestamp) FirstFourthEventTimestamp
  FROM EventOrdered
  WHERE ROW_NO = 4)
SELECT e.*
FROM Event e
JOIN FourthEvent ffe
  ON e.UserId = ffe.UserId
  AND e.Timestamp > ffe.FirstFourthEventTimestamp
ORDER BY e.UserId, e.Timestamp
0
Indy 8 kwiecień 2020, 10:57

Możesz użyć funkcji okna:

select *
from (
    select t.*, row_number() over(partition by user_id, event_id order by timestamp) rn
    from mytable t
) t
where rn > 4

Oto mała sztuczka, która usuwa numer wiersza z wyniku:

select (t).*
from (
    select t, row_number() over(partition by user_id, event_id order by timestamp) rn
    from mytable t
) x
where rn > 4
0
GMB 2 kwiecień 2020, 21:42

Możesz użyć łącznej liczby. Ta wersja zawiera czwarte wystąpienie:

select t.*
from (select t.*,
             count(*) filter (where event_id = 11) over (partition by user_id order by timestamp) as event_11_cnt
      from t
     ) t
where event_11_cnt >= 4;

filter jest poprawną składnią Postgres przez długi czas, ale zamiast tego możesz użyć:

select t.*
from (select t.*,
             sum( (event_id = 11)::int ) over (partition by user_id order by timestamp) as event_11_cnt
      from t
     ) t
where event_11_cnt >= 4;

Ta wersja nie:

where event_11_cnt > 4 or (event_11_cnt = 4 and event_id <> 11)

Alternatywna metoda:

select t.*
from t
where t.timestamp > (select t2.timestamp
                     from t t2
                     where t2.user_id = t.user_id and
                           t2.event_id = 11
                     order by t2.timestamp
                     limit 1 offset 3
                    );
0
Gordon Linoff 2 kwiecień 2020, 23:26