如何获得前期客户的折扣数量？ [英] How to get the discount number of customers in prior period?
问题描述
我有一个条件，我应该在前365天的时间内汇总客户数据。
I have a requirement where I supposed to roll customer data in the prior period of 365 days.
表格：
CREATE TABLE orders (
persistent_key_str character varying,
ord_id character varying(50),
ord_submitted_date date,
item_sku_id character varying(50),
item_extended_actual_price_amt numeric(18,2)
);
样本数据：
INSERT INTO orders VALUES
('01120736182','ORD6266073' ,'2010-12-08','100856-01',39.90),
('01120736182','ORD33997609' ,'2011-11-23','100265-01',49.99),
('01120736182','ORD33997609' ,'2011-11-23','200020-01',29.99),
('01120736182','ORD33997609' ,'2011-11-23','100817-01',44.99),
('01120736182','ORD89267964' ,'2012-12-05','200251-01',79.99),
('01120736182','ORD89267964' ,'2012-12-05','200269-01',59.99),
('01011679971','ORD89332495' ,'2012-12-05','200102-01',169.99),
('01120736182','ORD89267964' ,'2012-12-05','100907-01',89.99),
('01120736182','ORD89267964' ,'2012-12-05','200840-01',129.99),
('01120736182','ORD125155068','2013-07-27','201443-01',199.99),
('01120736182','ORD167230815','2014-06-05','200141-01',59.99),
('01011679971','ORD174927624','2014-08-16','201395-01',89.99),
('01000217334','ORD92524479' ,'2012-12-20','200021-01',29.99),
('01000217334','ORD95698491' ,'2013-01-08','200021-01',19.99),
('01000217334','ORD90683621' ,'2012-12-12','200021-01',29.990),
('01000217334','ORD92524479' ,'2012-12-20','200560-01',29.99),
('01000217334','ORD145035525','2013-12-09','200972-01',49.99),
('01000217334','ORD145035525','2013-12-09','100436-01',39.99),
('01000217334','ORD90683374' ,'2012-12-12','200284-01',39.99),
('01000217334','ORD139437285','2013-11-07','201794-01',134.99),
('01000827006','W02238550001','2010-06-11','HL 101077',349.000),
('01000827006','W01738200001','2009-12-10','EL 100310 BLK',119.96),
('01000954259','P00444170001','2009-12-03','PC 100455 BRN',389.99),
('01002319116','W02242430001','2010-06-12','TR 100966',35.99),
('01002319116','W02242430002','2010-06-12','EL 100985',99.99),
('01002319116','P00532470001','2010-05-04','HO 100482',49.99);
使用下面的查询，我试图按order_submitted_date获取不同客户的数量：
Using the query below I am trying to get the number of distinct customers by order_submitted_date:
select
g.order_date as "Ordered",
count(distinct o.persistent_key_str) as "customers"
from
generate_series(
(select min(ord_submitted_date) from orders),
(select max(ord_submitted_date) from orders),
'1 day'
) g (order_date)
left join
orders o on o.ord_submitted_date between g.order_date - interval '364 days'
and g.order_date
WHERE extract(year from ord_submitted_date) <= 2009
group by 1
order by 1
这是输出我希望如此。
Ordered Customers
2009-12-03 1
2009-12-10 1
当我执行上面的查询时，我得到了不正确的结果。
我怎么做这个权利？
When I execute the query above I get incorrect results.
How can I make this right?
推荐答案
要获得预期的输出（不同客户的数量）-仅需2009年的实际订单天：
To get your expected output ("the number of distinct customers") - only days with actual orders 2009:
SELECT ord_submitted_date, count(DISTINCT persistent_key_str) AS customers
FROM orders
WHERE ord_submitted_date >= '2009-1-1'
AND ord_submitted_date < '2010-1-1'
GROUP BY 1
ORDER BY 1;
以这种方式制定 WHERE 条件查询可精，并输入简单。
Formulate the WHERE conditions this way to make the query sargable, and input easy.
如果您想每天每天排（从最早的条目到最新的 订单），在2009年内：
If you want one row per day (from the earliest entry up to the latest in orders) - within 2009:
SELECT ord_submitted_date AS ordered
, count(DISTINCT o.persistent_key_str) AS customers
FROM (SELECT generate_series(min(ord_submitted_date) -- single query ...
, max(ord_submitted_date) -- ... to get min / max
, '1d')::date FROM orders) g (ord_submitted_date)
LEFT join orders o USING (ord_submitted_date)
WHERE ord_submitted_date >= '2009-1-1'
AND ord_submitted_date < '2010-1-1'
GROUP BY 1
ORDER BY 1;
SELECT extract(year from ord_submitted_date) AS year
, count(DISTINCT persistent_key_str) AS customers
FROM orders
GROUP BY 1
ORDER BY 1;
这篇关于如何获得前期客户的折扣数量？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
