有没有办法在 SQL 中检查某个间隔

huangapple go评论57阅读模式
英文:

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.

dbfiddle

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天的范围(DD+1D+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'

huangapple
  • 本文由 发表于 2023年2月19日 17:45:28
  • 转载请务必保留本文链接:https://go.coder-hub.com/75499223.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定