SQL-запрос Объединяет 3 таблицы, группирует по определенному столбцу и фильтрует по годам?

#sql #postgresql

Вопрос:

Каковы общие распределения в 2020 году по свойствам, которые были приобретены в 2019 году, с разбивкой по состоянию?

Пока это то, что у меня есть, и, похоже, я не правильно подытоживаю? какой шаг я пропускаю. Я не получаю сообщение об ошибке, но и не получаю ответа? Есть идеи?

 CREATE TABLE Property
(
    hmy INT   NOT NULL,
    scode VARCHAR   NOT NULL,
    saddr1 VARCHAR NOT NULL,
    saddr2 VARCHAR NOT NULL,
    scity VARCHAR NOT NULL,
    sstate VARCHAR NOT NULL,
    szipcode INT NOT NULL,
    PRIMARY KEY (hmy) 
);

CREATE TABLE PropInfo
(
    hmy INT   NOT NULL,
    hcode INT   NOT NULL,
    acquisitiondate DATE   NOT NULL,
    unitcount INT   NOT NULL,
    yearbuilt INT   NOT NULL,
    distributionentitycode VARCHAR   NOT NULL,
    PRIMARY KEY (hcode)
);

CREATE TABLE DistributionLog
(
    hmy INT   NOT NULL,
    hcode INT   NOT NULL,
    distributionDate DATE   NOT NULL,
    distributiontype VARCHAR   NOT NULL,
    distributionamount INT   NOT NULL,
    PRIMARY KEY (hmy)
);

SELECT SUM(DL.distributionamount) AS Total, PY.sstate, PI.acquisitiondate, DL.distributionamount 
FROM DistributionLog AS DL
JOIN PropInfo AS PI
ON PI.hmy = DL.hcode
JOIN Property AS PY
ON PY.hmy = PI.hmy
WHERE distributionDate BETWEEN '2020-1-1' AND '2020-12-31'
GROUP BY PY.sstate, PI.acquisitiondate, DL.distributionamount
ORDER BY PY.sstate;

Вот что было опробовано:

1. Какая таблица сообщает, когда было приобретено свойство? Можете ли вы включить структуру PropInfo и Property, а также описать их отношения?

2. @FereshtehAghaei Пожалуйста, предоставьте образец ввода-вывода. Решить вашу проблему будет легко.

3. @Linker Я добавил другие запросы к таблице и добавил изображение, каков результат

4. @zedfox Я добавил больше деталей, надеюсь, это будет более понятно, и изображение того, что получается

5. Ваша модель данных неясна. Похоже, что у одного свойства может быть несколько PropInfo записей, и это отбросит любые вычисления.

Ответ №1:

Вы можете попробовать что-то вроде этого:

 select p.sstate, sum(distributionamount)
from distributionlog d
inner join propinfo pi
  on d.hcode = pi.hcode
  and extract(year from acquisitiondate) = 2019
  and extract(year from distributiondate) = 2020
inner join property p on pi.hmy = p.hmy
group by p.sstate

В этом SQL мы объединяем 3 таблицы, как это делали и вы. Во время объединения мы указываем, что дата приобретения должна быть в 2019 году, а дата распространения — с 2020 года.

Ответ №2:

Поскольку вам нужна сумма (DL.distributionamount), DL.distributionamount не должен быть в предложении group by . Приведенный ниже запрос должен вернуть вам sstate wise sum (DL.distributionamount) для свойств, которые были приобретены в 2019 году и распространены в 2020 году.(связь между таблицами мне непонятна без выборочных данных. Итак, я предполагаю, что вы правильно выполнили объединение)

 SELECT  PY.sstate, SUM(DL.distributionamount) AS Total
FROM DistributionLog AS DL
JOIN PropInfo AS PI
ON PI.hmy = DL.hcode and PI.acquisitiondate BETWEEN '2019-1-1' AND '2019-12-31'
JOIN Property AS PY
ON PY.hmy = PI.hmy
WHERE distributionDate BETWEEN '2020-1-1' AND '2020-12-31'
GROUP BY PY.sstate
ORDER BY PY.sstate;