英文:
Is there anyway to check some interval in sql
问题
以下是你的翻译内容,已去除代码部分:
"Is there anyway to check for each sku if every 2 days average sales is bigger than for example 14 amount sold. I want to find date ranges, the percentage and amount it sold in those days.
for example for sku B in my example, it sold 15 at 2022-01-01
and 20 at 2022-01-02
and the average is 17.5 for these 2 days which is bigger than 14 therefore it will appear in my result and the change is 17.5 / 14 = 1.25.
Again for the next 2 days we have 20 at 2022-01-02
and 13 at 2022-01-03
. Therefore the average is 16.5 which is bigger than 14 and it will appear in the result
but for 13 at 2022-01-03
and 12 at 2022-01-04
and the average is about 12.5. Because 12.5 is not bigger than 14, it will not appear in the result.
my desired output with 14 amount example is:
sku start_date end_date amount_sold change_rate
B 2022-01-01 2022-01-02 17.5 1.25
B 2022-01-02 2022-01-03 16.5 1.17
D 2022-01-01 2022-01-02 28 2
I tried using CASE WHEN
but I know that it wont work for large data like one year:
SELECT *
FROM (
SELECT sku,
AVG(CASE WHEN date BETWEEN '2022-01-01' AND '2022-01-02' THEN amount END) AS first_in,
AVG(CASE WHEN date BETWEEN '2022-01-02' AND '2022-01-03' THEN amount END) AS second_in,
AVG(CASE WHEN date BETWEEN '2022-01-03' AND '2022-01-04' THEN amount END) AS third_in
FROM sales
GROUP BY sku
) AS t
WHERE first_in > 14
OR second_in > 14
OR third_in > 14
英文:
For example I have a table like this:
CREATE TABLE sales (
id int NOT NULL PRIMARY KEY,
sku text NOT NULL,
date date NOT NULL,
amount real NOT NULL,
CONSTRAINT date_sku UNIQUE (sku,date)
)
Is there anyway to check for each sku if every 2 days average sales is bigger than for example 14 amount sold. I want to find date ranges, the percentage and amount it sold in those days.
for example for sku B in my example, it sold 15 at 2022-01-01
and 20 at 2022-01-02
and the average is 17.5 for these 2 days which is bigger than 14 therefore it will appear in my result and the change is 17.5 / 14 = 1.25.
Again for the next 2 days we have 20 at 2022-01-02
and 13 at 2022-01-03
. Therefore the average is 16.5 which is bigger than 14 and it will appear in the result
but for 13 at 2022-01-03
and 12 at 2022-01-04
and the average is about 12.5. Because 12.5 is not bigger than 14, it will not appear in the result.
my desired output with 14 amount example is:
sku start_date end_date amount_sold change_rate
B 2022-01-01 2022-01-02 17.5 1.25
B 2022-01-02 2022-01-03 16.5 1.17
D 2022-01-01 2022-01-02 28 2
I tried using CASE WHEN
but I know that it wont work for large data like one year:
SELECT *
FROM (
SELECT sku,
AVG(CASE WHEN date BETWEEN '2022-01-01' AND '2022-01-02' THEN amount END) AS first_in,
AVG(CASE WHEN date BETWEEN '2022-01-02' AND '2022-01-03' THEN amount END) AS second_in,
AVG(CASE WHEN date BETWEEN '2022-01-03' AND '2022-01-04' THEN amount END) AS third_in
FROM sales
GROUP BY sku
) AS t
WHERE first_in > 14
OR second_in > 14
OR third_in > 14
答案1
得分: 1
使用LEAD
(或LAG
)通常可以检索下一条或上一条记录的数据。至少在你提出这个问题之前,这是我几天前做的事情。如果你需要超过1天的内容,其他窗口函数也适用于你的需求:
SELECT *, averageamount/14
FROM (
SELECT sku, date,
MAX(date) OVER w AS nextdate,
AVG(amount) OVER w AS averageAmount
FROM sales
WINDOW w AS (PARTITION BY sku ORDER BY date RANGE BETWEEN '0 day' PRECEDING AND '2 days' FOLLOWING )
) s
WHERE averageAmount > 14
上面的查询会选择所有长达3天的范围(D
、D+1
和 D+2
天)。如果你想去除少于3天的范围,可以添加以下附加条件:
AND nextdate >= date + interval '2 days'
英文:
As a general rule, use the LEAD
(or LAG
) to retrieve data from the next or previous record. At least this is what I did before you asked for possibly several days. Other window functions are suitable for your need if you want more than 1 day:
SELECT *, averageamount/14
FROM (
SELECT sku, date,
MAX(date) OVER w AS nextdate,
AVG(amount) OVER w AS averageAmount
FROM sales
WINDOW w AS (PARTITION BY sku ORDER BY date RANGE BETWEEN '0 day' PRECEDING AND '2 days' FOLLOWING )
) s
WHERE averageAmount > 14
This above select all the ranges that are up to 3 days long (days D
, D+1
and D+2
). You may want to remove the ranges that are less than 3 days long by appending the additional condition:
AND nextdate >= date + interval '2 days'
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论