SQL Server 选择倒数第二个X之后的第一个记录

mdfafbf1 于 2023-02-03 发布在其他

关注(0)|答案(3)|浏览(174)

我尝试获取每个用户（USER_ID）的X（DEF_ENDING）的倒数第二条记录之后的第一个BEG_PERIOD日期。
所以我有这个：
| 用户ID|开始_周期|结束_周期|定义_结束|
| - ------|- ------|- ------|- ------|
| 一百五十九|2022年7月1日|二○二二年七月三十一日|十|
| 一百五十九|二○二二年九月二十五日|二○二二年十月十五日|十|
| 一百五十九|二○二二年十一月一日|二○二二年十一月十三日||
| 一百五十九|二○二二年十一月十四日|二○二二年十二月二十一日|十|
| 一百五十九|2023年1月1日|二○二三年一月三十日|十|
| 四一四|二○二二年四月一日|二○二二年五月三十一日|十|
| 四一四|2022年7月1日|二○二二年九月三十日||
| 四一四|2022年10月1日|二○二二年十二月一日|十|
| 四百八十|2022年7月1日|二○二二年六月三十日||
| 四百八十|2022年7月1日|二○二二年八月三十日|十|
| 四百八十|二○二二年九月二日|二○二二年十一月一日|十|
| 五零三|二○二二年三月十五日|二○二二年六月十六日|十|
| 五零三|二○二二年七月十九日|二○二二年七月二十三日||
| 五零三|二○二二年七月二十四日|二○二二年十月三十一日||
| 五零三|二○二二年十一月一日|二○二二年十二月二十一日|十|
我需要的日期是粗体字
你能帮帮我吗？
我试过这个，但我只得到最新的日期：（

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

来源：https://stackoverflow.com/questions/75304690/select-the-first-record-after-the-last-but-one-x

3条答案

按热度按时间

9jyewag01#

这应基于样本数据：

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;

赞(0）回复(0）举报 2023-02-03

toe950272#

使用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

See demo

赞(0）回复(0）举报 2023-02-03

5jdjgkvh3#

我认为，这在SQL Server 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|开始_周期|结束_周期|定义_结束|数量|倒数第二|
| - ------|- ------|- ------|- ------|- ------|- ------|
| 一百五十九|2022年7月1日|二〇二二年七月三十一日|十|四个|2023年1月1日|
| 一百五十九|二〇二二年九月二十五日|二〇二二年十月十五日|十|三个|2023年1月1日|
| 一百五十九|二○二二年十一月一日|二〇二二年十一月十三日|零|第二章|2023年1月1日|
| 一百五十九|二〇二二年十一月十四日|二〇二二年十二月二十一日|十|第二章|2023年1月1日|
| 一百五十九|2023年1月1日|2023年1月30日|十|1个|2023年1月1日|
| 四一四|2022年4月1日|二○二二年五月三十一日|十|第二章|2022年7月1日|
| 四一四|2022年7月1日|2022年9月30日|零|1个|2022年7月1日|
| 四一四|二○二二年十月一日|二○二二年十二月一日|十|1个|2022年7月1日|
| 四百八十|2022年7月1日|2022年6月30日|零|第二章|2022年9月2日|
| 四百八十|2022年7月1日|2022年8月30日|十|第二章|2022年9月2日|
| 四百八十|2022年9月2日|二○二二年十一月一日|十|1个|2022年9月2日|
| 五零三|2022年3月15日|二〇二二年六月十六日|十|第二章|2022年7月19日|
| 五零三|2022年7月19日|二〇二二年七月二十三日|零|1个|2022年7月19日|
| 五零三|二〇二二年七月二十四日|二〇二二年十月三十一日|零|1个|2022年7月19日|
| 五零三|二○二二年十一月一日|二〇二二年十二月二十一日|十|1个|2022年7月19日|
关于并行数据仓库，
如前所述here，2012年之前的非PDW版本的SQL Server不支持带有聚合函数（如MIN）的ORDER BY子句。
与从SQL Server 2005开始提供的基本实施相比，窗口函数支持在2012年得到了大幅扩展。这些扩展在合并到Box产品之前已在Parallel Data Warehouse中提供。

赞(0）回复(0）举报 2023-02-03

我来回答

SQL Server 选择倒数第二个X之后的第一个记录

3条答案

相关问题

热门标签

最新问答