计算每个国家/地区每天的销售份额 [英] Calculate the sales share per country per day
问题描述
创建表销售(id 序列主键,事件日期日期,国家VARCHAR,销售十进制);插入销售(事件日期,国家,销售)价值观('2020-02-08', 'DE', '500'),('2020-02-08', 'FR', '900'),('2020-02-08', 'NL', '700'),('2020-03-20', '美国', '600'),('2020-03-20', 'DE', '500'),('2020-04-15', 'NL', '300'),('2020-04-15', 'FR', '800'),('2020-04-15', 'NL', '100');
预期结果:
event_date |国家 |sales_share_per_country_per_day |------------|-----------|---------------------------------------|------------2020-02-08 |德 |0.24 (=500/2100) |2020-02-08 |法国 |0.43 (=900/2100) |2020-02-08 |荷兰 |0.33 (=700/2100) |------------|-----------|---------------------------------------|------------2020-03-20 |美国 |0.55 (=600/1100) |2020-03-20 |德 |0.45 (=500/1100) |------------|-----------|---------------------------------------|------------2020-04-15 |荷兰 |0.25 (=300/1200) |2020-04-15 |法国 |0.67 (=800/1200) |2020-04-15 |荷兰 |0.08 (=100/1100) |
我想计算每个国家/地区每天的销售份额.
因此,我尝试使用此查询:
SELECTs.event_date,s.国家,s.销售,SUM(s.sales) OVER (PARTITION BY s.country) AS sales_share_per_day来自销售按 1,2,3 分组按 1 排序;
然而,我无法达到预期的效果.
你知道我必须如何修改查询吗?
注意:最后我将需要这个查询来进行 redshift.
但是,据我所知,对于窗口函数,redshift 使用 postgresSQL 语法.
因此,我在问题中标记了 redshift 和 postgresSQL.
如果这个假设有误,请随时纠正我.
使用 sales_share_per_day 四舍五入到小数点后两位
<块引用>创建表销售(id 序列主键,事件日期日期,国家VARCHAR,销售十进制);插入销售(事件日期,国家,销售)价值观('2020-02-08', 'DE', '500'),('2020-02-08', 'FR', '900'),('2020-02-08', 'NL', '700'),('2020-03-20', '美国', '600'),('2020-03-20', 'DE', '500'),('2020-04-15', 'NL', '300'),('2020-04-15', 'FR', '800'),('2020-04-15', 'NL', '100');
<块引用>
选择s.event_date,s.国家,s.销售,round(s.sales/sum(s.sales) OVER (PARTITION BY event_date ),2) AS sales_share_per_day来自销售按 1 排序;
<块引用>
event_date | 国家 | 销售 | sales_share_per_day |
---|---|---|---|
2020-02-08 | DE | 500 | 0.24 |
2020-02-08 | FR | 900 | 0.43 |
2020-02-08 | NL | 700 | 0.33 |
2020-03-20 | 美国 | 600 | 0.55 |
2020-03-20 | DE | 500 | 0.45 |
2020-04-15 | NL | 300 | 0.25 |
2020-04-15 | FR | 800 | 0.67 |
2020-04-15 | NL | 100 | 0.08 |
db<>fiddle 这里/p>
未四舍五入:
<块引用>创建表销售(id 序列主键,事件日期日期,国家VARCHAR,销售十进制);插入销售(事件日期,国家,销售)价值观('2020-02-08', 'DE', '500'),('2020-02-08', 'FR', '900'),('2020-02-08', 'NL', '700'),('2020-03-20', '美国', '600'),('2020-03-20', 'DE', '500'),('2020-04-15', 'NL', '300'),('2020-04-15', 'FR', '800'),('2020-04-15', 'NL', '100');
<块引用>
选择s.event_date,s.国家,s.销售,s.sales/sum(s.sales) OVER (PARTITION BY event_date ) AS sales_share_per_day来自销售按 1 排序;
<块引用>
event_date | 国家 | 销售 | sales_share_per_day |
---|---|---|---|
2020-02-08 | DE | 500 | 0.23809523809523809524 |
2020-02-08 | FR | 900 | 0.42857142857142857143 |
2020-02-08 | NL | 700 | 0.33333333333333333333 |
2020-03-20 | 美国 | 600 | 0.54545454545454545455 |
2020-03-20 | DE | 500 | 0.45454545454545454545 |
2020-04-15 | NL | 300 | 0.25000000000000000000 |
2020-04-15 | FR | 800 | 0.66666666666666666667 |
2020-04-15 | NL | 100 | 0.08333333333333333333 |
db<>fiddle 这里/p>
CREATE TABLE sales (
id SERIAL PRIMARY KEY,
event_date DATE,
country VARCHAR,
sales DECIMAL
);
INSERT INTO sales
(event_date, country, sales)
VALUES
('2020-02-08', 'DE', '500'),
('2020-02-08', 'FR', '900'),
('2020-02-08', 'NL', '700'),
('2020-03-20', 'US', '600'),
('2020-03-20', 'DE', '500'),
('2020-04-15', 'NL', '300'),
('2020-04-15', 'FR', '800'),
('2020-04-15', 'NL', '100');
Expected Result:
event_date | country | sales_share_per_country_per_day |
------------|-----------|---------------------------------------|-------------
2020-02-08 | DE | 0.24 (=500/2100) |
2020-02-08 | FR | 0.43 (=900/2100) |
2020-02-08 | NL | 0.33 (=700/2100) |
------------|-----------|---------------------------------------|-------------
2020-03-20 | US | 0.55 (=600/1100) |
2020-03-20 | DE | 0.45 (=500/1100) |
------------|-----------|---------------------------------------|-------------
2020-04-15 | NL | 0.25 (=300/1200) |
2020-04-15 | FR | 0.67 (=800/1200) |
2020-04-15 | NL | 0.08 (=100/1100) |
I want to calculate the sales share per country per day.
Therefore, I tried to go with this query:
SELECT
s.event_date,
s.country,
s.sales,
SUM(s.sales) OVER (PARTITION BY s.country) AS sales_share_per_day
FROM sales s
GROUP BY 1,2,3
ORDER BY 1;
However, I could not achieve the expected result.
Do you have any idea how I have to modify the query?
NOTE: In the end I will need this query for redshift.
However, as far as I know for window functions redshift uses postgresSQL syntax.
Therefore I tagged redshift and postgresSQL in the question.
Feel free to correct me if this assumption is wrong.
With sales_share_per_day rounding off to two digits after decimal point
CREATE TABLE sales ( id SERIAL PRIMARY KEY, event_date DATE, country VARCHAR, sales DECIMAL ); INSERT INTO sales (event_date, country, sales) VALUES ('2020-02-08', 'DE', '500'), ('2020-02-08', 'FR', '900'), ('2020-02-08', 'NL', '700'), ('2020-03-20', 'US', '600'), ('2020-03-20', 'DE', '500'), ('2020-04-15', 'NL', '300'), ('2020-04-15', 'FR', '800'), ('2020-04-15', 'NL', '100');
SELECT s.event_date, s.country, s.sales, round(s.sales/sum(s.sales) OVER (PARTITION BY event_date ),2) AS sales_share_per_day FROM sales s ORDER BY 1;
event_date country sales sales_share_per_day 2020-02-08 DE 500 0.24 2020-02-08 FR 900 0.43 2020-02-08 NL 700 0.33 2020-03-20 US 600 0.55 2020-03-20 DE 500 0.45 2020-04-15 NL 300 0.25 2020-04-15 FR 800 0.67 2020-04-15 NL 100 0.08
db<>fiddle here
Without rounding off:
CREATE TABLE sales ( id SERIAL PRIMARY KEY, event_date DATE, country VARCHAR, sales DECIMAL ); INSERT INTO sales (event_date, country, sales) VALUES ('2020-02-08', 'DE', '500'), ('2020-02-08', 'FR', '900'), ('2020-02-08', 'NL', '700'), ('2020-03-20', 'US', '600'), ('2020-03-20', 'DE', '500'), ('2020-04-15', 'NL', '300'), ('2020-04-15', 'FR', '800'), ('2020-04-15', 'NL', '100');
SELECT s.event_date, s.country, s.sales, s.sales/sum(s.sales) OVER (PARTITION BY event_date ) AS sales_share_per_day FROM sales s ORDER BY 1;
event_date country sales sales_share_per_day 2020-02-08 DE 500 0.23809523809523809524 2020-02-08 FR 900 0.42857142857142857143 2020-02-08 NL 700 0.33333333333333333333 2020-03-20 US 600 0.54545454545454545455 2020-03-20 DE 500 0.45454545454545454545 2020-04-15 NL 300 0.25000000000000000000 2020-04-15 FR 800 0.66666666666666666667 2020-04-15 NL 100 0.08333333333333333333
db<>fiddle here
这篇关于计算每个国家/地区每天的销售份额的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!