比较两个时间戳的值，并将它们放在结果的两列中

Question

DB-Fiddle

CREATE TABLE operations (
    id SERIAL PRIMARY KEY,
    time_stamp DATE,
    product VARCHAR,
    plan_week VARCHAR,
    quantity DECIMAL
);

INSERT INTO operations
(time_stamp, product, plan_week, quantity
)
VALUES 
('2020-01-01', 'Product_A', 'CW01', '125'),
('2020-01-01', 'Product_B', 'CW01', '300'),
('2020-01-01', 'Product_C', 'CW08', '700'),
('2020-01-01', 'Product_D', 'CW01', '900'),
('2020-01-01', 'Product_G', 'CW05', '600'),
('2020-01-01', 'Product_J', 'CW01', '465'),

('2020-03-15', 'Product_A', 'CW01', '570'),
('2020-03-15', 'Product_C', 'CW02', '150'),
('2020-03-15', 'Product_E', 'CW02', '325'),
('2020-03-15', 'Product_G', 'CW01', '482'),
('2020-03-15', 'Product_J', 'CW12', '323');

预期结果：

time_stamp  |    product  |   plan_week  |  quantity  |  first_plan  |  last_plan  |
----------  |-------------|--------------|------------|--------------|-------------|---
2020-01-01  |  Product_A  |      CW01    |     125    |     CW01     |     CW01    |
2020-03-15  |  Product_A  |      CW01    |     570    |     CW01     |     CW01    |
------------|-------------|--------------|------------|--------------|-------------|---            
2020-01-01  |  Product_B  |      CW01    |     300    |     CW01     |     CW01    |
------------|-------------|--------------|------------|--------------|-------------|---    
2020-01-01  |  Product_C  |      CW08    |     700    |     CW08     |     CW02    |
2020-03-15  |  Product_C  |      CW02    |     150    |     CW08     |     CW02    |
------------|-------------|--------------|------------|--------------|-------------|---    
2020-01-01  |  Product_D  |      CW01    |     900    |     CW01     |     CW01    |
------------|-------------|--------------|------------|--------------|-------------|---    
2020-03-15  |  Product_E  |      CW02    |     325    |     CW02     |     CW02    |
------------|-------------|--------------|------------|--------------|-------------|---  
2020-01-01  |  Product_G  |      CW05    |     600    |     CW05     |     CW01    |
2020-03-15  |  Product_G  |      CW01    |     482    |     CW05     |     CW01    |
------------|-------------|--------------|------------|--------------|-------------|---   
2020-01-01  |  Product_J  |      CW01    |     465    |     CW01     |     CW12    |
2020-03-15  |  Product_J  |      CW12    |     323    |     CW01     |     CW12    |

我想比较一下每个plan_week的两个timestamps，并将它们排列在彼此下面，就像您在预期结果中看到的那样。

在列first_plan中，我想列出第一个时间戳的周。

在列last_plan中，我想列出最后一次泰米邮票的那一周。

我目前正在使用这个查询来实现postgresSQL中的结果

SELECT 
time_stamp,
product,
plan_week,
quantity,
(FIRST_VALUE(plan_week) OVER (PARTITION BY product ORDER BY time_stamp ASC)) first_plan,
(FIRST_VALUE(plan_week) OVER (PARTITION BY product ORDER BY time_stamp DESC)) last_plan
FROM operations;

但是，当我将这个sql应用于amazon-redshift时，我得到：

ERROR: Aggregate window functions with an ORDER BY clause require a frame clause

如何修改查询以使其在红移中工作？

Answer 1

手册解释了框架条款是什么：

• value.html
• synopsis.html

(这是窗口应该向前或向后看的行数。)

​

你可能想要像..。

SELECT 
  time_stamp,
  product,
  plan_week,
  quantity,
  FIRST_VALUE(plan_week)
    OVER (
      PARTITION BY product
          ORDER BY time_stamp
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    )
      AS first_plan,
  LAST_VALUE(plan_week)
    OVER (
      PARTITION BY product
          ORDER BY time_stamp
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    )
      AS last_plan
FROM
  operations

注意，我使用的是LAST_VALUE()，而不是反转ORDER BY。通常，对于多个窗口函数，最好保留相同的window子句。它使乐观主义者的生活变得容易一些，这对你有好处。