在表中按日期聚合表值[SQL]
在表中按日期聚合表值[SQL]
提问于 2019-05-24 11:57:11
我有一个包含cust_id、sku、date、order_number的表(如果是该客户的第一个、第二个……)和数量。我的最终目标是找到每个客户在第一年的总花费(确切的日期将相对于每个客户)。
sales数据表为rld_sales格式，包含以下列：
- cust_id
- sku，
- date of purchase，
- order number (如果是该客户的第1个或第2个...)
-- CREATES TABLE OF CUSTOMER_ID AND Y1, Y2 [END DATES]
CREATE TABLE customer_timetable AS (SELECT
rld_sales. "customer_id" AS cust_id,
CAST(date(rld_sales."date") + INTERVAL '1 year' AS DATE) as y1,
CAST(date(rld_sales."date") + INTERVAL '2 year' AS DATE) as y2
FROM
rld_sales
WHERE
transaction_order = 1
GROUP BY
rld_sales. "customer_id",
date
);
-- JOINS CUSTOMER_TIMETABLE WITH RLD_SALES
CREATE TABLE t1 AS (
SELECT
customer_timetable.cust_id,
customer_timetable.y1,
customer_timetable.y2,
rld_sales.*
FROM
customer_timetable
JOIN rld_sales ON (customer_timetable.cust_id = rld_sales.customer_id)
);
SELECT
t1. "cust_id",
SUM(t1. "amount"),
SUM(t2. "amount")
FROM
t1
WHERE
CAST(date AS DATE) BETWEEN y1
AND y2
LEFT JOIN (
SELECT
t1. "amount"
FROM
t1
WHERE
CAST(date AS DATE) > t1. "y2") t2 ON t1. "cust_id" = t2. "cust_id"
GROUP BY
t1. "cust_id"
SELECT
*
FROM
customer_timetable;到目前为止，我在创建中间表和一次连接一个表方面只取得了很小的成功，但我觉得必须有一种更优雅的方法来在一个查询中实现所有这些。
回答 2
Stack Overflow用户
回答已采纳
发布于 2019-05-24 17:28:04
如果我猜你正在使用MySQL数据库，下面的查询将为你提供一些编写标准SQL以满足你的需求的指导。
SELECT
A.customer_id,
CASE
WHEN A."date" >= DATE_ADD(CAST(NOW() AS DATE), INTERVAL -1 YEAR) THEN 'Last_Year'
WHEN A."date" >= DATE_ADD(CAST(NOW() AS DATE), INTERVAL -2 YEAR) THEN 'Last_Previous_Year'
ELSE 'Before_That'
END 'Year',
SUM(amount) -- Not sure your amount in which table. Add Table Alias before the coulmn name if required
FROM rld_sales A
INNER JOIN customer_timetable B ON A.customer_id = B.cust_id
GROUP BY
A.customer_id,
CASE
WHEN A."date" >= DATE_ADD(CAST(NOW() AS DATE), INTERVAL -1 YEAR) THEN 'Last_Year'
WHEN A."date" >= DATE_ADD(CAST(NOW() AS DATE), INTERVAL -2 YEAR) THEN 'Last_Previous_Year'
ELSE 'Before_That'
END 要获得每个列ID的年值，请使用以下查询-
SELECT A.customer_id,
SUM(CASE WHEN A."Year" = 'Last_Year' THEN Amount ELSE 0 END) 'Last_Year',
SUM(CASE WHEN A."Year" = 'Last_Previous_Year' THEN Amount ELSE 0 END) 'Last_Previous_Year',
SUM(CASE WHEN A."Year" = 'Before_That' THEN Amount ELSE 0 END) 'Before_That'
FROM
(
SELECT
A.customer_id,
CASE
WHEN A."date" >= DATE_ADD(CAST(NOW() AS DATE), INTERVAL -1 YEAR) THEN 'Last_Year'
WHEN A."date" >= DATE_ADD(CAST(NOW() AS DATE), INTERVAL -2 YEAR) THEN 'Last_Previous_Year'
ELSE 'Before_That'
END 'Year',
SUM(amount) Amount -- Not sure your amount in which table. Add Table Alias before the coulmn name if required
FROM rld_sales A
INNER JOIN customer_timetable B ON A.customer_id = B.cust_id
GROUP BY
A.customer_id,
CASE
WHEN A."date" >= DATE_ADD(CAST(NOW() AS DATE), INTERVAL -1 YEAR) THEN 'Last_Year'
WHEN A."date" >= DATE_ADD(CAST(NOW() AS DATE), INTERVAL -2 YEAR) THEN 'Last_Previous_Year'
ELSE 'Before_That'
END
)A
GROUP BY A.customer_idStack Overflow用户
发布于 2019-05-24 19:42:58
我会这样写：
select s.customer_id, sum(s.amount)
from rld_sales s
where s.date < (select min(s2.date) + interval 1 year
from rld_sales s2
where s2.customer_id = s.customer_id
)
group by s.customer_id;临时表似乎只会使这个查询更加复杂。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56285666
复制相关文章
相似问题