sql-server - SQL-选择一个值大于第一个值日的行

Question

我在 SQL 中有下表-

user_id    purchase_date      revenues  
-------------------------------------
A           2018-01-01          30
A           2018-01-02          28
A           2018-01-04          45
A           2018-01-06          55
B           2018-01-03          20
B           2018-01-05          21   

而且我必须只选择用户收入大于第一天收入的行。

意味着预期的结果应该是-

user_id    purchase_date   revenues 
-------------------------------------
A           2018-01-04          45
A           2018-01-06          55
B           2018-01-05          21 

我刚刚开始学习 SQL，我可以自己进行每个查询（按日期或每个用户的收入），但我似乎无法掌握这种复杂性的窍门。

非常感谢！

标签： sql-serverselect

Answer 1

您可以使用窗口函数 ROW_NUMBER()

with A as
(
    select user_id, purchase_date, revenues
    from 
        (values
        ('A',           '2018-01-01',          30),
        ('A',           '2018-01-02',          28),
        ('A',           '2018-01-04',          45),
        ('A',           '2018-01-06',          55),
        ('B',           '2018-01-03',          20),
        ('B',           '2018-01-05',          21)) as T(user_id, purchase_date, revenues)   
),
B as (
    select 
        ROW_NUMBER() over (partition by user_id order by purchase_date) as n, 
        user_id, purchase_date, revenues from A
),
C as (
    select * from B where n = 1
)
select
    A.user_id, A.purchase_date, A.revenues
from
    A inner join C on (A.user_id = C.user_id and A.revenues > C.revenues)

你也可以使用 FIRST_VALUE 函数

with A as
(
    select user_id, purchase_date, revenues
    from 
        (values
        ('A',           '2018-01-01',          30),
        ('A',           '2018-01-02',          28),
        ('A',           '2018-01-04',          45),
        ('A',           '2018-01-06',          55),
        ('B',           '2018-01-03',          20),
        ('B',           '2018-01-05',          21)) as T(user_id, purchase_date, revenues)   
),
B as (
    select 
        user_id, purchase_date, revenues, 
        FIRST_VALUE(revenues) over (partition by user_id order by purchase_date) as n
    from A
    )
select user_id, purchase_date, revenues from B where revenues > n;