Я пытаюсь получить первую дату BEG_PERIOD
сразу после предпоследней записи X (DEF_ENDING
) каждого пользователя (USER_ID
).
Итак, у меня есть это:
Даты, которые мне нужны, выделены жирным шрифтом
Вы можете помочь мне?
Я пробовал это, но я получаю только последние даты :(
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
Я пытаюсь использовать SQL Server 2008, это школьный сервер.
Я не понимаю, по какому правилу вы выбираете даты. Дважды последний ряд, один раз предпоследний, один раз предпоследний. Дважды это X-ряд, дважды не-X ряд. Я не вижу закономерности.
так... проблема: пользователи работают в течение определенного периода времени... (могут быть дни или недели). Иногда они уходят из школы, но не навсегда, в других случаях уход постоянный... (они прекращают трудовые отношения), но они могут снова вернуться в школу, поэтому после предпоследнего ухода необходимо первое свидание.
Это должно работать на основе данных примера:
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) не включены».
Посмотрите, исправил ли мой group by
это.
Нет, выдает ту же ошибку.
Вы пробовали оба запроса? Это сообщение кажется ошибкой 2008 года.
@tg_rs Обратите внимание, что ранее я вставил group by
не в то место. Вот ссылка на работающую скрипку: dbfiddle.uk/jVSdoMHt По какой-то причине SQL Server в данный момент не работает, но у вас не должно возникнуть проблем, если вы переключитесь на него, когда он снова появится.
Вот и все!! С «существует» он работает отлично. Большое спасибо @shawnt00
Попробуйте следующее, используя оконные функции 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) не включены».
Я создал ROW_NUMBER, но можно ли получить правильную дату без «LAG»?
Насколько я помню, LAG поддерживается с SQL Server 2012, в любом случае, может быть обходной путь для имитации LAG, я постараюсь вам его предоставить.
SQL Server 2008 больше не поддерживается, пора обновиться :)
Я знаю. И я думаю, что учитель тоже знает :) Спасибо
Я думаю, это работает на сервере 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
Результат
О параллельном хранилище данных
как упоминалось здесь, версии SQL Server без PDW до 2012 года не поддерживают предложение ORDER BY с агрегатными функциями, такими как MIN.
Поддержка оконных функций была значительно расширена в 2012 году по сравнению с базовой реализацией, доступной, начиная с SQL Server 2005. Расширения были доступны в Parallel Data Warehouse, прежде чем они были включены в коробочный продукт.
Этот также подходит для версии 2008 года. Большое спасибо :)
Какую СУБД вы используете?