限制相關子查詢中的行

我的查詢有一個大問題和兩個小問題，我需要幫助。

首先，我想要一個 SQL（如果重要的話是 postgres）查詢，它獲取SUM最後兩個水果評級，並為原始表上的每個水果列創建一個新列，並具有相應的結果。條件是它必須在過去發生（在目前行日期之前），我只希望“rush_order”的最後 2 條記錄和列必須具有值“yes”。

現在我的查詢獲取所有記錄，但我只想要最後 2 個。我嘗試LIMIT在 4 個子查詢中的每一個中使用，並且也嘗試過OFFSET 0但FETCH FIRST 2 ROWS ONLY無法使其工作。如果有一種方法可以使用OFFSET它，則可以消除使用 date < current_row.date 比較的需要，因為該表將始終按升序排列。

其次，如果這裡有關於如何使用的建議，CASE我認為它會快得多。我按原樣為每一行執行 4 個子查詢；有太多的重複。

第三，我認為另一種解決方法是將 name1 和 rating1 堆疊在 name2 的頂部，將 rating2 堆疊在彼此的頂部。沒有必要，但我願意以另一種方式解決這個問題。

謝謝

CREATE TABLE IF NOT EXISTS fruit (
  fruit_id INTEGER  NOT NULL ,
  name1 varchar(50) NOT NULL,
   name2 varchar(50) NOT NULL,
  rating1 integer NOT NULL,
   rating2 integer NOT NULL,
    delivered_on date NOT NULL,
    rush_order varchar(3) NOT NULL,

  PRIMARY KEY  (fruit_id)
)

INSERT INTO fruit (fruit_id, name1, name2, rating1, rating2, delivered_on, rush_order) VALUES
(1, 'Apple', 'Banana', 5, 4,'2015-10-01', 'yes'),
(2, 'Pear', 'Banana', 3, 4,'2015-10-27', 'yes'),
(3, 'Apple', 'Pear', 5, 4,'2015-10-29', 'no'),
(4, 'Banana', 'Apple', 2, 5,'2015-11-27', 'yes'),
(5, 'Banana', 'Apple', 4, 1,'2015-11-29', 'yes'),
(6, 'Apple', 'Banana', 4, 2,'2015-12-02', 'no'),
(7, 'Pear', 'Apple', 3, 5,'2015-12-20', 'yes');

SELECT *,
   COALESCE(
       (SELECT sum(f2.rating1)
   FROM fruit f2
       WHERE f1.name1 = f2.name1 AND f1.delivered_on > f2.delivered_on),0) +
   COALESCE(
       (SELECT sum(f3.rating2)
   FROM fruit f3
       WHERE f1.name1 = f3.name2 AND f1.delivered_on > f3.delivered_on) ,0) 
           AS fruit1_rating,
   COALESCE(
       (SELECT sum(f4.rating2)
   FROM fruit f4
       WHERE f1.name2 = f4.name2 AND f1.delivered_on > f4.delivered_on),0) +
   COALESCE(
       (SELECT sum(f5.rating1)
   FROM fruit f5
       WHERE f1.name2 = f5.name1 AND f1.delivered_on > f5.delivered_on) ,0) AS fruit2_rating
FROM fruit f1;

預期結果：

子查詢中的 Selected 表在匯總之前必須先受到限制，但我無法重現您的預期結果

CREATE TABLE IF NOT EXISTS fruit (
   fruit_id INTEGER  NOT NULL ,
   name1 varchar(50) NOT NULL,
    name2 varchar(50) NOT NULL,
   rating1 integer NOT NULL,
    rating2 integer NOT NULL,
     delivered_on date NOT NULL,
     rush_order varchar(3) NOT NULL,

   PRIMARY KEY  (fruit_id)
 );

INSERT INTO fruit (fruit_id, name1, name2, rating1, rating2, delivered_on, rush_order) VALUES
(1, 'Apple', 'Banana', 5, 4,'2015-10-01', 'yes'),
(2, 'Pear', 'Banana', 3, 4,'2015-10-27', 'yes'),
(3, 'Apple', 'Pear', 5, 4,'2015-10-29', 'no'),
(4, 'Banana', 'Apple', 2, 5,'2015-11-27', 'yes'),
(5, 'Banana', 'Apple', 4, 1,'2015-11-29', 'yes'),
(6, 'Apple', 'Banana', 4, 2,'2015-12-02', 'no'),
(7, 'Pear', 'Apple', 3, 5,'2015-12-20', 'yes');

 SELECT *,
    COALESCE(
        (SELECT sum(f2.rating1)
    FROM (SELECt * FROM fruit f2
        WHERE f1.name1 = f2.name1 AND f1.delivered_on > f2.delivered_on AND f2.rush_order = 'yes' ORDER BY f2.delivered_on LIMIT 2) f2),0) +
        COALESCE(
        (SELECT sum(f3.rating1)
    FROM (SELECt * FROM fruit f3
        WHERE f1.name1 = f3.name1 AND f1.delivered_on > f3.delivered_on AND f3.rush_order = 'yes' ORDER BY f3.delivered_on LIMIT 2) f3),0) AS fruit1_rating,
            COALESCE(
        (SELECT sum(f4.rating1)
    FROM (SELECt * FROM fruit f4
        WHERE f1.name1 = f4.name2 AND f1.delivered_on > f4.delivered_on AND f4.rush_order = 'yes' ORDER BY f4.delivered_on LIMIT 2) f4),0) +
        COALESCE(
        (SELECT sum(f5.rating1)
    FROM (SELECt * FROM fruit f5
        WHERE f1.name1 = f5.name2 AND f1.delivered_on > f5.delivered_on AND f5.rush_order = 'yes' ORDER BY f5.delivered_on LIMIT 2) f5),0) AS fruit2_rating
FROM fruit f1;

水果ID | 名稱1 | 名稱2 | 評級1 | 評級2 | 已傳遞 | rush_order | 水果1_rating | fruit2_rating
-------: | :----- | :----- | ------: | ------: | :----------- | :--------- | ------------: | ------------:
 1 | 蘋果 | 香蕉 | 5 | 4 | 2015-10-01 | 是的 | 0 | 0
 2 | 梨 | 香蕉 | 3 | 4 | 2015-10-27 | 是的 | 0 | 0
 3 | 蘋果 | 梨 | 5 | 4 | 2015-10-29 | 沒有 | 10 | 0
 4 | 香蕉 | 蘋果 | 2 | 5 | 2015-11-27 | 是的 | 0 | 16
 5 | 香蕉 | 蘋果 | 4 | 1 | 2015-11-29 | 是的 | 4 | 16
 6 | 蘋果 | 香蕉 | 4 | 2 | 2015-12-02 | 沒有 | 10 | 12
 7 | 梨 | 蘋果 | 3 | 5 | 2015-12-20 | 是的 | 6 | 0

db<>在這裡擺弄

尋找這個：

WITH 
cte1 AS ( SELECT fruit_id, 1 column_id, name1 "name", rating1 rating, delivered_on, rush_order
         FROM fruit
         UNION ALL
         SELECT fruit_id, 2, name2, rating2, delivered_on, rush_order
         FROM fruit ),
cte2 AS ( SELECT *, SUM((rush_order = 'yes')::INT) OVER (PARTITION BY "name" ORDER BY delivered_on) num
         FROM cte1 ),
cte3 AS ( SELECT *, SUM(rating * (rush_order = 'yes')::INT) 
                   OVER (PARTITION BY "name" 
                         ORDER BY num 
                         RANGE BETWEEN 1 PRECEDING 
                         AND CURRENT ROW) sum_rating
         FROM cte2 )
SELECT fruit_id,
      t1."name" name1,
      t2."name" name2,
      t1.rating rating1,
      t2.rating rating2,
      delivered_on, 
      rush_order,
      t1.sum_rating fruit1_rating,
      t2.sum_rating fruit2_rating
FROM cte3 t1
JOIN cte3 t2 USING (fruit_id, delivered_on, rush_order)
WHERE t1.column_id = 1 
 AND t2.column_id = 2
ORDER BY delivered_on;

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=8d3062a1d5e5d4405272bee96adaacf7

引用自：https://dba.stackexchange.com/questions/290643