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

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

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:

  1. sku start_date end_date amount_sold change_rate
  2. B 2022-01-01 2022-01-02 17.5 1.25
  3. B 2022-01-02 2022-01-03 16.5 1.17
  4. 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:

  1. SELECT *
  2. FROM (
  3. SELECT sku,
  4. AVG(CASE WHEN date BETWEEN '2022-01-01' AND '2022-01-02' THEN amount END) AS first_in,
  5. AVG(CASE WHEN date BETWEEN '2022-01-02' AND '2022-01-03' THEN amount END) AS second_in,
  6. AVG(CASE WHEN date BETWEEN '2022-01-03' AND '2022-01-04' THEN amount END) AS third_in
  7. FROM sales
  8. GROUP BY sku
  9. ) AS t
  10. WHERE first_in > 14
  11. OR second_in > 14
  12. OR third_in > 14
英文:

For example I have a table like this:

  1. CREATE TABLE sales (
  2. id int NOT NULL PRIMARY KEY,
  3. sku text NOT NULL,
  4. date date NOT NULL,
  5. amount real NOT NULL,
  6. CONSTRAINT date_sku UNIQUE (sku,date)
  7. )

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:

  1. sku start_date end_date amount_sold change_rate
  2. B 2022-01-01 2022-01-02 17.5 1.25
  3. B 2022-01-02 2022-01-03 16.5 1.17
  4. 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:

  1. SELECT *
  2. FROM (
  3. SELECT sku,
  4. AVG(CASE WHEN date BETWEEN '2022-01-01' AND '2022-01-02' THEN amount END) AS first_in,
  5. AVG(CASE WHEN date BETWEEN '2022-01-02' AND '2022-01-03' THEN amount END) AS second_in,
  6. AVG(CASE WHEN date BETWEEN '2022-01-03' AND '2022-01-04' THEN amount END) AS third_in
  7. FROM sales
  8. GROUP BY sku
  9. ) AS t
  10. WHERE first_in > 14
  11. OR second_in > 14
  12. OR third_in > 14

答案1

得分: 1

使用LEAD(或LAG)通常可以检索下一条或上一条记录的数据。至少在你提出这个问题之前,这是我几天前做的事情。如果你需要超过1天的内容,其他窗口函数也适用于你的需求:

  1. SELECT *, averageamount/14
  2. FROM (
  3. SELECT sku, date,
  4. MAX(date) OVER w AS nextdate,
  5. AVG(amount) OVER w AS averageAmount
  6. FROM sales
  7. WINDOW w AS (PARTITION BY sku ORDER BY date RANGE BETWEEN '0 day' PRECEDING AND '2 days' FOLLOWING )
  8. ) s
  9. WHERE averageAmount > 14

上面的查询会选择所有长达3天的范围(DD+1D+2 天)。如果你想去除少于3天的范围,可以添加以下附加条件:

  1. 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:

  1. SELECT *, averageamount/14
  2. FROM (
  3. SELECT sku, date,
  4. MAX(date) OVER w AS nextdate,
  5. AVG(amount) OVER w AS averageAmount
  6. FROM sales
  7. WINDOW w AS (PARTITION BY sku ORDER BY date RANGE BETWEEN '0 day' PRECEDING AND '2 days' FOLLOWING )
  8. ) s
  9. 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:

  1. 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:

确定