Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

-- Исходные данные заказ - клиент - сумма заказа
CREATE TABLE test (zakaz INT, client VARCHAR(255), summa INT);
INSERT INTO test VALUES
(1,'Иван',1),
(2,'Иван',12),
(3,'Игорь',33),
(4,'Игорь',22),
(5,'Игорь',11),
(6,'Сергей',10),
(7,'Сергей',3),
(8,'Павел',35);
SELECT * FROM test;
8 rows affected



zakaz
client
summa


1
Иван
1

2
Иван
12

3
Игорь
33

4
Игорь
22

5
Игорь
11

6
Сергей
10

7
Сергей
3

8
Павел
35


-- Расчёт средних - просто демонстрация
SELECT *,
       AVG(summa) OVER () avg_cafe,
       AVG(summa) OVER (PARTITION BY client) avg_client
FROM test


zakaz
client
summa
avg_cafe
avg_client


2
Иван
12
15.8750000000000000
6.5000000000000000

1
Иван
1
15.8750000000000000
6.5000000000000000

4
Игорь
22
15.8750000000000000
22.0000000000000000

5
Игорь
11
15.8750000000000000
22.0000000000000000

3
Игорь
33
15.8750000000000000
22.0000000000000000

8
Павел
35
15.8750000000000000
35.0000000000000000

6
Сергей
10
15.8750000000000000
6.5000000000000000

7
Сергей
3
15.8750000000000000
6.5000000000000000


-- Получение нужной выборки
WITH cte AS ( SELECT *,
                     AVG(summa) OVER () avg_cafe,
                     AVG(summa) OVER (PARTITION BY client) avg_client
              FROM test )
SELECT DISTINCT client
FROM cte
WHERE avg_client > avg_cafe;


client


Игорь

Павел

zakaz	client	summa
1	Иван	1
2	Иван	12
3	Игорь	33
4	Игорь	22
5	Игорь	11
6	Сергей	10
7	Сергей	3
8	Павел	35

zakaz	client	summa	avg_cafe	avg_client
2	Иван	12	15.8750000000000000	6.5000000000000000
1	Иван	1	15.8750000000000000	6.5000000000000000
4	Игорь	22	15.8750000000000000	22.0000000000000000
5	Игорь	11	15.8750000000000000	22.0000000000000000
3	Игорь	33	15.8750000000000000	22.0000000000000000
8	Павел	35	15.8750000000000000	35.0000000000000000
6	Сергей	10	15.8750000000000000	6.5000000000000000
7	Сергей	3	15.8750000000000000	6.5000000000000000