想在使用聚合函数时添加第三列，不属于group by

Question

这是我查询后的输出步骤1:

select customer_id, plan_id, start_date, row_number() over (partition by customer_idorder by plan_id) as row_order
from f.subscriptions
where start_date < '2021-01-01'
customer_id plan_id start_date  row_order
1   0   2020-08-01T00:00:00.000Z    1
1   1   2020-08-08T00:00:00.000Z    2
2   0   2020-09-20T00:00:00.000Z    1
2   3   2020-09-27T00:00:00.000Z    2
3   0   2020-01-13T00:00:00.000Z    1
3   1   2020-01-20T00:00:00.000Z    2
4   0   2020-01-17T00:00:00.000Z    1
4   1   2020-01-24T00:00:00.000Z    2
4   4   2020-04-21T00:00:00.000Z    3
5   0   2020-08-03T00:00:00.000Z    1
5   1   2020-08-10T00:00:00.000Z    2
6   0   2020-12-23T00:00:00.000Z    1
6   1   2020-12-30T00:00:00.000Z    2
7   0   2020-02-05T00:00:00.000Z    1
7   1   2020-02-12T00:00:00.000Z    2
7   2   2020-05-22T00:00:00.000Z    3
8   0   2020-06-11T00:00:00.000Z    1
8   1   2020-06-18T00:00:00.000Z    2
8   2   2020-08-03T00:00:00.000Z    3

步骤2:

select customer_id, max(row_order)
from (select customer_id, plan_id, start_date
, row_number() over (partition by customer_id order by plan_id) as row_order
from f.subscriptions
where start_date < '2021-01-01') t1
group by customer_id

输出:

customer_id max
1   2
2   2
3   2
4   3
5   2
6   2
7   3
8   3

现在我还想在第二个输出中添加列(plan_id)。怎么做呢?

Answer 1

如果我理解正确的话，您需要每个具有最新start_date的客户的完整行。在Postgres中，最好的方法是使用distinct on:

select distinct on (customer_id) s.*
from f.subscriptions s
where start_date < '2021-01-01'
order by customer_id start_date desc;

Answer 2

这取决于你需要和想要什么，因为每个id有多个计划

你还可以看到更多的聚合函数

CREATE TABLE subscriptions
("customer_id" int, "plan_id" int, "start_date" TIMESTAMP)
;

INSERT INTO subscriptions
("customer_id", "plan_id", "start_date")
VALUES
(1, 0, '2020-08-01T00:00:00.000Z'),
(1, 1, '2020-08-08T00:00:00.000Z'),
(2, 0, '2020-09-20T00:00:00.000Z'),
(2, 3, '2020-09-27T00:00:00.000Z'),
(3, 0, '2020-01-13T00:00:00.000Z'),
(3, 1, '2020-01-20T00:00:00.000Z'),
(4, 0, '2020-01-17T00:00:00.000Z'),
(4, 1, '2020-01-24T00:00:00.000Z'),
(4, 4, '2020-04-21T00:00:00.000Z'),
(5, 0, '2020-08-03T00:00:00.000Z'),
(5, 1, '2020-08-10T00:00:00.000Z'),
(6, 0, '2020-12-23T00:00:00.000Z'),
(6, 1, '2020-12-30T00:00:00.000Z'),
(7, 0, '2020-02-05T00:00:00.000Z'),
(7, 1, '2020-02-12T00:00:00.000Z'),
(7, 2, '2020-05-22T00:00:00.000Z'),
(8, 0, '2020-06-11T00:00:00.000Z'),
(8, 1, '2020-06-18T00:00:00.000Z'),
(8, 2, '2020-08-03T00:00:00.000Z')
;

select customer_id, max(plan_id), MAX(start_date), max(row_order)
from (select customer_id, plan_id, start_date
, row_number() over (partition by customer_id order by plan_id) as row_order
from subscriptions
where start_date < '2021-01-01') t1
group by customer_id

customer_id | Max | Max----------: |——:|:------------------ |——::1 | 1 | 2020-08-08 00:00:00 | 22 | 3 | 2020-09-27 00:00:00 | 23 | 1 | 2020-01-20 00:00:00 | 24 | 4 | 2020-04-21 00:00:00 | 35 | 1 | 2020-08-10 00:00:00 | 26 | 1 | 2020-12-30 00:00:00 | 27 | 2 | 2020-05-22 00:00:00 | 38 | 2 | 2020-08-03 00:00:00 | 3

select customer_id, ARRAY_AGG (plan_id || '-' || start_date), max(row_order)
from (select customer_id, plan_id, start_date
, row_number() over (partition by customer_id order by plan_id) as row_order
from subscriptions
where start_date < '2021-01-01') t1
group by customer_id

customer_id | array_agg | Max----------: | :------------------------------------------------------------------------ | --:1 | {"0-2020-08-01 00:00:00"; "1-2020-08 00:00:00"} |2 | {"0-2020-09-20 00:00:00"; "3-2020-09-27 00:00:00"} |3 | {"0-2020-01-13 00:00:00"; "1-2020-01-20 00:00:00"} |4 | {"0-2020-01-17 00:00:00";1-2020-01-24 00:00:00";4-2020-04-21 00:00:00"}5 | {"0-2020-08-03 00:00:00"; "1-2020-08-10 00:00:00"} |6 | {"0-2020-12-23 00:00:00"; "1-2020-12-30 00:00:00"} |7 | {"0-2020-02-05 00:00:00";1-2020-02-12 00:00:00";2-2020-05-22 00:00:00"}8 |{"0-2020-06-11 00:00:00"1-2020-06-18 00:00:00","2-2020-08-03 00:00:00"} | 3

db<此处小提琴>