SQL 표의 두 기간을 비교하여 판매 결과를 얻습니다.
이 표는 수출업자, 제품, 마크, 팔레트, 수량 등을 포함한 실제 표의 간단한 예입니다.
하지만 이 테이블이 제 고민을 가장 쉽게 알 수 있을 것 같아요.
+----+--------+----------+----------+----------+------------+
| Id | Seller | Products | Marks | Quantity | Date |
+----+--------+----------+----------+----------+------------+
| 01 | John | Watch | Apple | 5 | 2017-01-01 |
| 02 | Peter | Mouse | Apple | 21 | 2017-01-01 |
| 02 | Peter | Mouse | Logitech | 10 | 2017-01-02 |
| 01 | John | Mouse | Logitech | 15 | 2017-01-03 |
| 02 | Peter | Keyboard | Logitech | 11 | 2017-01-05 |
| 01 | John | Keyboard | Apple | 10 | 2017-01-06 |
| 02 | Peter | Keyboard | Apple | 14 | 2017-01-06 |
| 01 | John | Keyboard | Logitech | 12 | 2017-01-06 |
| 01 | John | Keyboard | Apple | 17 | 2017-01-07 |
| 01 | John | Mouse | Logitech | 13 | 2017-01-08 |
| 01 | John | Watch | Apple | 6 | 2017-01-09 |
| . | . | . | . | . | . |
| 01 | Peter | Mouse | Logitech | 5 | 2018-01-01 |
| 02 | Steve | Keyboard | Apple | 7 | 2018-01-03 |
| 02 | Peter | Watch | Apple | 8 | 2018-01-03 |
| 01 | John | Mouse | Logitech | 6 | 2018-01-04 |
| 02 | Peter | Watch | Samsung | 9 | 2018-01-04 |
| 01 | John | Keyboard | Apple | 18 | 2018-01-08 |
| 01 | John | Watch | Apple | 7 | 2018-01-08 |
| 02 | Peter | Phone | Xiaomi | 19 | 2018-01-08 |
| 01 | John | Keyboard | Logitech | 15 | 2018-01-06 |
| 01 | Peter | TV | Samsung | 20 | 2018-01-07 |
| 01 | Steve | Display | LG | 11 | 2018-01-08 |
| 01 | John | Display | Samsung | 14 | 2018-01-08 |
| . | . | . | . | . | . |
+----+--------+----------+----------+----------+------------+
판매자가 판매 결과를 알 수 있도록 동일한 표에서 두 기간(예: 2018년 1월과 2017년 1월)을 비교해야 합니다.
이런 사람도 있고...
+----+--------+----------+----------+------------+------------+-----------+
| Id | Seller | Products | Marks | Total 2017 | Total 2018 | Diference |
+----+--------+----------+----------+------------+------------+-----------+
| 01 | John | Watch | Apple | 11 | 7 | -4 |
| 01 | John | Mouse | Logitech | 28 | 6 | -22 |
| 01 | John | Keyboard | Apple | 17 | 18 | 1 |
| 01 | John | Keyboard | Logitech | 12 | 15 | 3 |
| 01 | John | Display | Samsung | 0 | 14 | 14 |
| 02 | Peter | Mouse | Apple | 21 | 0 | -21 |
| 02 | Peter | Mouse | Logitech | 10 | 5 | -5 |
| 02 | Peter | Keyboard | Logitech | 11 | 0 | -11 |
| . | . | . | . | . | . | . |
| . | . | . | . | . | . | . |
| . | . | . | . | . | . | . |
+----+--------+----------+----------+------------+------------+-----------+
이러한 질문을 시도했지만 반복적으로 값이 표시되며 셀러가 제대로 표시되지 않습니다.
SELECT *, (Q1 - Q2) AS Diference
FROM
(SELECT Id, Seller, Products, Marks, SUM(Quantity) AS Q1
WHERE Date >= '2018-01-01' AND Date <= '2018-01-31' AND Seller IS NOT NULL
GROUP BY Id, Marks, Products) AS A
INNER JOIN
(SELECT Id AS Id2, Seller, Products, Marks, SUM(Quantity) AS Q2
WHERE Date >= '2017-01-01' AND Date <= '2017-01-31' AND Seller IS NOT NULL
GROUP BY Id, Marks, Products) AS B
ON A.Id = B.Id2
SELECT *, (Q1 - Q2) AS Diference,
(CASE
WHEN Id IS NULL THEN Id2
ELSE Id
END) AS Id3,
(CASE
WHEN Id IS NULL THEN Seller2
ELSE Seller
END) AS Seller3
FROM
(SELECT Id, Seller, Products, Marks, SUM(Quantity) AS Q1
WHERE Date >= '2018-01-01' AND Date <= '2018-01-31' AND Seller IS NOT NULL
GROUP BY Id, Marks, Products) AS A
INNER JOIN
(SELECT Id AS Id2, Seller AS Seller2, Products AS Products2, Marks, SUM(Quantity) AS Q2
WHERE Date >= '2017-01-01' AND Date <= '2017-01-31' AND Seller IS NOT NULL
GROUP BY Id, Marks, Products) AS B
ON A.Id = B.Id2
어떤 도움은 감사할 것이다.
잘 부탁드립니다.
조건부 집계를 사용합니다.
select Id, Seller, Products, Marks,
sum(case when year(date) = 2017 then quantity end) as quantity_2017,
sum(case when year(date) = 2018 then quantity end) as quantity_2018,
(sum(case when year(date) = 2018 then quantity end) -
sum(case when year(date) = 2017 then quantity end)
) as diff
from realtable
group by Id, Seller, Products, Marks;
드디어, 훌륭하게 작동합니다!
고마워 고든 리노프!
SELECT ex.id_exportador,
exp.exportador,
ex.id_producto, pd.producto,
SUM((CASE WHEN (s.fechasalida >= $P{fechaInicial} AND s.fechasalida <= $P{fechaFinal}) THEN val.bultos ELSE 0 END)) AS b1,
SUM((CASE WHEN (s.fechasalida >= $P{anteriorInicial} AND s.fechasalida <= $P{anteriorFinal}) THEN val.bultos ELSE 0 END)) AS b2,
SUM((CASE WHEN (s.fechasalida >= $P{fechaInicial} AND s.fechasalida <= $P{fechaFinal}) THEN val.kg_brutos ELSE 0 END)) AS kgb1,
SUM((CASE WHEN (s.fechasalida >= $P{anteriorInicial} AND s.fechasalida <= $P{anteriorFinal}) THEN val.kg_brutos ELSE 0 END)) AS kgb2,
SUM((CASE WHEN (s.fechasalida >= $P{fechaInicial} AND s.fechasalida <= $P{fechaFinal}) THEN val.kg_netos ELSE 0 END)) AS kgn1,
SUM((CASE WHEN (s.fechasalida >= $P{anteriorInicial} AND s.fechasalida <= $P{anteriorFinal}) THEN val.kg_netos ELSE 0 END)) AS kgn2,
SUM((CASE WHEN (s.fechasalida >= $P{fechaInicial} AND s.fechasalida <= $P{fechaFinal}) THEN val.palets ELSE 0 END)) AS pal1,
SUM((CASE WHEN (s.fechasalida >= $P{anteriorInicial} AND s.fechasalida <= $P{anteriorFinal}) THEN val.palets ELSE 0 END)) AS pal2,
SUM((CASE WHEN (s.fechasalida >= $P{fechaInicial} AND s.fechasalida <= $P{fechaFinal}) THEN val.europalets ELSE 0 END)) AS epal1,
SUM((CASE WHEN (s.fechasalida >= $P{anteriorInicial} AND s.fechasalida <= $P{anteriorFinal}) THEN val.europalets ELSE 0 END)) AS epal2
FROM servicios s
LEFT JOIN expedientes ex
ON s.codservicio = ex.codservicio
LEFT JOIN exportadores exp
ON ex.id_exportador = exp.id_exportador
LEFT JOIN vales val
ON ex.id_expediente = val.id_expediente
LEFT JOIN productos pd
ON ex.id_producto = pd.id_producto
WHERE ((s.fechasalida >= $P{fechaInicial} AND s.fechasalida <= $P{fechaFinal}) OR (s.fechasalida >= $P{anteriorInicial} AND s.fechasalida <= $P{anteriorFinal})) AND ex.id_exportador IS NOT NULL
GROUP BY ex.id_exportador, ex.id_producto
언급URL : https://stackoverflow.com/questions/54225445/sql-compare-two-periods-in-a-table-and-obtain-sales-results
'programing' 카테고리의 다른 글
| MySQL/MariaDB에서 LONGTEXT 필드를 JSON으로 반환하는 방법 (0) | 2023.01.30 |
|---|---|
| HashSet/HashMap에 중복된 값을 추가하면 이전 값이 대체됩니까? (0) | 2023.01.30 |
| '메모리 스톰프'란? (0) | 2023.01.30 |
| 개체를 포함하는 어레이를 루프하여 해당 속성에 액세스하는 방법 (0) | 2023.01.30 |
| Java에서 목록을 "인스턴스화"하고 있습니까? (0) | 2023.01.20 |