Выберите первую запись после предпоследнего X

Я пытаюсь получить первую дату BEG_PERIOD сразу после предпоследней записи X (DEF_ENDING) каждого пользователя (USER_ID).

Итак, у меня есть это:

ID ПОЛЬЗОВАТЕЛЯ BEG_PERIOD END_PERIOD DEF_ENDING 159 01-07-2022 31-07-2022 Икс 159 25-09-2022 15-10-2022 Икс 159 01-11-2022 13-11-2022 159 14-11-2022 21-12-2022 Икс 159 01-01-2023 30-01-2023 Икс 414 01-04-2022 31-05-2022 Икс 414 01-07-2022 30-09-2022 414 01-10-2022 01-12-2022 Икс 480 01-07-2022 30-06-2022 480 01-07-2022 30-08-2022 Икс 480 02-09-2022 01-11-2022 Икс 503 15-03-2022 16-06-2022 Икс 503 19-07-2022 23-07-2022 503 24-07-2022 31-10-2022 503 01-11-2022 21-12-2022 Икс

Даты, которые мне нужны, выделены жирным шрифтом

Вы можете помочь мне?

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

SELECT
    p.USER_ID,
    p.BEG_PERIOD
FROM
    PERIODS p
    INNER JOIN PERIODS p2 ON
        p.USER_ID = p2.USER_ID
        AND
        p.BEG_PERIOD = (
            SELECT
                MAX( BEG_PERIOD )
            FROM
                PERIODS
            WHERE
                PERIODS.USER_ID = p.USER_ID
        )
WHERE
    p.USER_ID > 10

Какую СУБД вы используете?

Dai 01.02.2023 02:15

Я пытаюсь использовать SQL Server 2008, это школьный сервер.

tg_rs 01.02.2023 12:08

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

Thorsten Kettner 02.02.2023 00:07

так... проблема: пользователи работают в течение определенного периода времени... (могут быть дни или недели). Иногда они уходят из школы, но не навсегда, в других случаях уход постоянный... (они прекращают трудовые отношения), но они могут снова вернуться в школу, поэтому после предпоследнего ухода необходимо первое свидание.

tg_rs 02.02.2023 11:06
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
4
109
3
Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

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

Это должно работать на основе данных примера:

with data as (
    select *,
        sum(case when DEF_ENDING = 'X' then 1 end)
            over (partition by USER_ID order by BEG_PERIOD desc) as grp
    from PERIODS
)
select
    USER_ID,
    min(BEG_PERIOD) as BEG_PERIOD,
    min(END_PERIOD) as END_PERIOD,
    min(DEF_ENDING) as DEF_ENDING
from data
where grp = 1
group by USER_ID;

Если вы не можете полагаться на то, что две даты являются минимумами, то:

with data as (
    select *,
        sum(case when DEF_ENDING = 'X' then 1 end)
            over (partition by USER_ID order by BEG_PERIOD desc) as grp
    from PERIODS
), data2 as (
    select *,
        row_number() over (partition by USER_ID order by BEG_PERIOD) as rn
    from data
    where grp = 1
)
select *
from data2
where rn = 1;

Это также можно сделать полностью с помощью подзапросов, если это более уместно на уровне вашего класса:

select USER_ID, min(BEG_PERIOD), min(END_PERIOD), min(DEF_ENDING)
from periods p1
where p1.BEG_PERIOD > (
    select max(BEG_PERIOD)
    from periods p2
    where p2.USER_ID = p1.USER_ID and p2.DEF_ENDING = 'X'
        and exists (
            select 1
            from periods p3
            where p3.USER_ID = p2.USER_ID and p3.DEF_ENDING = 'X'
                and p3.BEG_PERIOD > p2.BEG_PERIOD
        )
    )
group by USER_ID;

Спасибо за внимание. В обоих результатах выдается одна и та же ошибка: «Функции хранилища параллельных данных (PDW) не включены».

tg_rs 01.02.2023 12:12

Посмотрите, исправил ли мой group by это.

shawnt00 01.02.2023 13:17

Нет, выдает ту же ошибку.

tg_rs 01.02.2023 17:29

Вы пробовали оба запроса? Это сообщение кажется ошибкой 2008 года.

shawnt00 01.02.2023 20:49

@tg_rs Обратите внимание, что ранее я вставил group by не в то место. Вот ссылка на работающую скрипку: dbfiddle.uk/jVSdoMHt По какой-то причине SQL Server в данный момент не работает, но у вас не должно возникнуть проблем, если вы переключитесь на него, когда он снова появится.

shawnt00 01.02.2023 23:46
dbfiddle.uk/AgwmnIB8
shawnt00 02.02.2023 10:28

Вот и все!! С «существует» он работает отлично. Большое спасибо @shawnt00

tg_rs 02.02.2023 10:52

Попробуйте следующее, используя оконные функции ROW_NUMBER и LAG:

/* this to assign row numbers only for rows where def_ending = 'X' */
with order_def_ending as 
(
  select *,
   case def_ending when 'X' then
    row_number() over (partition by user_id order by 
                        case def_ending when 'X' then 1 else 2 end, 
                        end_period desc)
    else null end rn,
    lag(def_ending, 1, def_ending) over (partition by user_id order by end_period) pde /* previous end_period value */
  from yourTbl
),
lag_rn as
(
  select *, 
    lag(rn) over (partition by user_id order by end_period) prn /* previous row_number value */
  from order_def_ending
)
select user_id, beg_period, end_period, def_ending
from lag_rn
where (
        prn = 2 or /* when there are multiple rows with def_ending = 'X' */
        (prn = 1 and rn is null) /* when there is only one row with def_ending = 'X' */
      ) and pde = 'X' /* ensure that the previous value of def_ending is = 'X' */
order by user_id, end_period

Посмотреть демо

Спасибо за ваше время. Результат: «Функции хранилища параллельных данных (PDW) не включены».

tg_rs 01.02.2023 12:09

Я создал ROW_NUMBER, но можно ли получить правильную дату без «LAG»?

tg_rs 01.02.2023 12:21

Насколько я помню, LAG поддерживается с SQL Server 2012, в любом случае, может быть обходной путь для имитации LAG, я постараюсь вам его предоставить.

ahmed 01.02.2023 12:24

SQL Server 2008 больше не поддерживается, пора обновиться :)

ahmed 01.02.2023 12:25

Я знаю. И я думаю, что учитель тоже знает :) Спасибо

tg_rs 01.02.2023 13:07

Я думаю, это работает на сервере SQL 2008

with periods as(
select USER_ID, cast(BEG_PERIOD as date)BEG_PERIOD,cast(END_PERIOD as date)END_PERIOD,DEF_ENDING
from (values
 (159,'01-07-2022','31-07-2022','X')
,(159,'25-09-2022','15-10-2022','X')
,(159,'01-11-2022','13-11-2022',null)
,(159,'14-11-2022','21-12-2022','X')
,(159,'01-01-2023','30-01-2023','X')
,(414,'01-04-2022','31-05-2022','X')
,(414,'01-07-2022','30-09-2022',null)
,(414,'01-10-2022','01-12-2022','X')
,(480,'01-07-2022','30-06-2022',null)
,(480,'01-07-2022','30-08-2022','X')
,(480,'02-09-2022','01-11-2022','X')
,(503,'15-03-2022','16-06-2022','X')
,(503,'19-07-2022','23-07-2022',null)
,(503,'24-07-2022','31-10-2022',null)
,(503,'01-11-2022','21-12-2022','X')
)t(USER_ID, BEG_PERIOD, END_PERIOD, DEF_ENDING)
)
,cte as (
select * 
   ,(select sum(case when def_ending='X' then 1 else 0 end) 
     from periods t2 where t2.user_id=t1.USER_ID and t2.BEG_PERIOD>=t1.BEG_PERIOD
    ) N -- last but one has N=2, all next N=1 (reverse order of counts)
from periods t1
)
select * 
   ,(select min(t2.BEG_PERIOD) 
     from cte t2 where t2.user_id=t1.USER_ID and t2.N=1
    ) LastButOne  -- first after last but one with N=1
from cte t1

Результат

ID ПОЛЬЗОВАТЕЛЯ BEG_PERIOD END_PERIOD DEF_ENDING Н Предпоследний 159 2022-07-01 2022-07-31 Икс 4 2023-01-01 159 2022-09-25 2022-10-15 Икс 3 2023-01-01 159 2022-11-01 2022-11-13 НУЛЕВОЙ 2 2023-01-01 159 2022-11-14 2022-12-21 Икс 2 2023-01-01 159 2023-01-01 2023-01-30 Икс 1 2023-01-01 414 2022-04-01 2022-05-31 Икс 2 2022-07-01 414 2022-07-01 2022-09-30 НУЛЕВОЙ 1 2022-07-01 414 2022-10-01 2022-12-01 Икс 1 2022-07-01 480 2022-07-01 2022-06-30 НУЛЕВОЙ 2 2022-09-02 480 2022-07-01 2022-08-30 Икс 2 2022-09-02 480 2022-09-02 2022-11-01 Икс 1 2022-09-02 503 2022-03-15 2022-06-16 Икс 2 2022-07-19 503 2022-07-19 2022-07-23 НУЛЕВОЙ 1 2022-07-19 503 2022-07-24 2022-10-31 НУЛЕВОЙ 1 2022-07-19 503 2022-11-01 2022-12-21 Икс 1 2022-07-19

О параллельном хранилище данных
как упоминалось здесь, версии SQL Server без PDW до 2012 года не поддерживают предложение ORDER BY с агрегатными функциями, такими как MIN.
Поддержка оконных функций была значительно расширена в 2012 году по сравнению с базовой реализацией, доступной, начиная с SQL Server 2005. Расширения были доступны в Parallel Data Warehouse, прежде чем они были включены в коробочный продукт.

Этот также подходит для версии 2008 года. Большое спасибо :)

tg_rs 02.02.2023 10:57

Другие вопросы по теме