Как показать максимальное количество для каждой комбинации клиента и продукта в определенном состоянии в Postgresql?

Я только недавно начал изучать Postgresql. У меня есть таблица с названием "продажи":

create table sales
    (
        cust    varchar(20),
        prod    varchar(20),
        day integer,
        month   integer,
        year    integer,
        state   char(2),
        quant   integer
    )

insert into sales values ('Bloom', 'Pepsi', 2, 12, 2001, 'NY', 4232);
insert into sales values ('Knuth', 'Bread', 23, 5, 2005, 'PA', 4167);
insert into sales values ('Emily', 'Pepsi', 22, 1, 2006, 'CT', 4404);
insert into sales values ('Emily', 'Fruits', 11, 1, 2000, 'NJ', 4369);
insert into sales values ('Helen', 'Milk', 7, 11, 2006, 'CT', 210);
......

Это выглядит так: введите описание изображения здесь И всего 500 строк.

Теперь я хочу использовать запрос для реализации этого:

Для каждой комбинации клиента и продукта выведите максимальные объемы продаж для Нью-Йорка и минимальные объемы продаж для Нью-Джерси и Коннектикут в 3 отдельных столбца. Как и в первом отчете, отобразите соответствующие даты (т. Е. Даты этих максимальных и минимальных объемов продаж). Кроме того, для Коннектикута и Нью-Джерси включают только продажи, произошедшие после 2000 г .; для Нью-Йорка включают все продажи.

Должно получиться так: введите описание изображения здесь

Я пробовал следующий запрос:

SELECT
    cust customer,
    prod product,
    MAX(CASE WHEN rn3 = 1 THEN quant END) NY_MAX,
    MAX(CASE WHEN rn3 = 1  THEN TO_DATE(year || '-' || month || '-' || day, 'YYYY-MM-DD') END) date,

    MIN(CASE WHEN rn2 = 1  THEN quant END) NJ_MIN,
    MIN(CASE WHEN rn2 = 1 THEN TO_DATE(year || '-' || month || '-' || day, 'YYYY-MM-DD') END) date,

    MIN(CASE WHEN rn1 = 1  THEN quant END) CT_MIN,
    MIN(CASE WHEN rn1 = 1 THEN TO_DATE(year || '-' || month || '-' || day, 'YYYY-MM-DD') END) date

FROM (
    SELECT
        *,
        ROW_NUMBER() OVER(PARTITION BY cust, prod ORDER BY quant) rn1,
        ROW_NUMBER() OVER(PARTITION BY cust, prod ORDER BY quant) rn2,
        ROW_NUMBER() OVER(PARTITION BY cust, prod ORDER BY quant DESC) rn3

    FROM sales 
) x
WHERE rn1 = 1   OR rn2 = 1   or rn3 = 1 
GROUP BY cust, prod;

Это результат: введите описание изображения здесь

Это неправильно, потому что он показывает мне максимальное и минимальное количество всех состояний, а не конкретного состояния, которое я хочу. И я понятия не имею, как относиться к году, как к вопросу, как мне.


postgresql join group-by row-number case-when
person Wikiz Vito    schedule 08.10.2019    source источник
comment
Образцы данных лучше представлять в виде форматированного текста не как снимки экрана. См. здесь несколько советов о том, как создавать красивые таблицы.   -  person a_horse_with_no_name    schedule 08.10.2019
comment
Я бы сделал это с помощью LATERAL соединения для простоты.   -  person Laurenz Albe    schedule 08.10.2019

Ответы (1)


arrow_upward
1
arrow_downward

Мы можем справиться с этим, используя отдельные CTE вместе с календарной таблицей:

WITH custprod AS (
    SELECT DISTINCT cust, prod
    FROM sales
),
ny_sales AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY cust, prod ORDER BY quant DESC) rn
    FROM sales
    WHERE state = 'NY'
),
nj_sales AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY cust, prod ORDER BY quant) rn
    FROM sales
    WHERE state = 'NJ'
),
ct_sales AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY cust, prod ORDER BY quant) rn
    FROM sales
    WHERE state = 'CT'
)

SELECT
    cp.cust,
    cp.prod,
    nys.quant AS ny_max,
    nys.year::text || '-' || nys.month::text || '-' || nys.day::text AS ny_date,
    njs.quant AS nj_max,
    njs.year::text || '-' || njs.month::text || '-' || njs.day::text AS nj_date,
    cts.quant AS ct_max,
    cts.year::text || '-' || cts.month::text || '-' || cts.day::text AS ct_date
FROM custprod cp
LEFT JOIN ny_sales nys
    ON cp.cust = nys.cust AND cp.prod = nys.prod AND nys.rn = 1
LEFT JOIN nj_sales njs
    ON cp.cust = njs.cust AND cp.prod = njs.prod AND njs.rn = 1
LEFT JOIN ct_sales cts
    ON cp.cust = cts.cust AND cp.prod = cts.prod AND cts.rn = 1
ORDER BY
    cp.cust,
    cp.prod;

Примечание. Вы не предоставили исчерпывающие образцы данных, но похоже, что приведенное выше работает в демонстрационной ссылке ниже.

Демо

person Tim Biegeleisen    schedule 08.10.2019
comment
Ценю вашу помощь!!! Это также работает со всеми моими данными. Еще одна вещь, как включить только продажи, которые произошли после 2000 года для Коннектикута и Нью-Джерси, и включить все продажи для Нью-Йорка ??? - person Wikiz Vito; 08.10.2019
comment
@WikizVito По сути, вы задаете совершенно новый вопрос, и вам действительно стоит открыть новый вопрос. - person Tim Biegeleisen; 09.10.2019
comment
Хорошо ... Но я уже сам решил этот новый вопрос. Большое тебе спасибо!! - person Wikiz Vito; 09.10.2019