删除订单距离距离SQL错误的距离超过2分钟时，postgresql错误[42803]:错误:在哪里不允许聚集功能

Question

我在删除比2分钟的距离下的订单时会遇到以下错误，但是我不能使用HAVING，因为子查询中没有GROUP BY。

我是否正确接近此操作，我应该 GROUP做什么才能做到这一点吗？

SQL错误[42803]：错误：

中不允许汇总功能

SELECT customer_id,
MAX(created_at) last_order_date,
MAX(created_at) + ((SELECT EXTRACT(MINUTE FROM MAX(created_at)-MIN(created_at)) 
FROM (SELECT customer_id, created_at
FROM (SELECT customer_id, created_at, rank() over (partition by customer_id order by created_at desc) lasttwo
FROM orders) sub
WHERE sub.lasttwo <= 2
AND SUM(EXTRACT(MINUTE FROM MAX(created_at)-MIN(created_at))) > 2) s2) ::text||' minute')::INTERVAL AS nextdate,
(SELECT AVG(total_price - total_tax) 
FROM (SELECT customer_id, created_at, total_price, total_tax
FROM (SELECT customer_id, created_at, total_price, total_tax, rank() over (partition by customer_id order by created_at desc) lasttwo
FROM orders) sub
WHERE sub.lasttwo <= 2
AND SUM(EXTRACT(MINUTE FROM MAX(created_at)-MIN(created_at))) > 2) s2) nextvalue 
FROM orders 
GROUP BY customer_id

Answer 1

好吧，我使用CTE重写了您的查询，并修复了聚合。在这里是：

with
sub as (
  SELECT customer_id, created_at, total_price, total_tax,
    rank() over (partition by customer_id order by created_at desc) lasttwo
  FROM orders
),
s2 as (
  SELECT customer_id, created_at, total_price, total_tax
  FROM sub
  WHERE sub.lasttwo <= 2
  GROUP BY customer_id, created_at, total_price, total_tax -- fix #1
  HAVING SUM(EXTRACT(MINUTE FROM MAX(created_at)-MIN(created_at))) > 2 -- fix #2
),
subx as (
  SELECT customer_id, created_at,
  rank() over (partition by customer_id order by created_at desc) lasttwo
  FROM orders
),
s2x as (
  SELECT customer_id, created_at
  FROM subx
  WHERE sub.lasttwo <= 2
  GROUP BY customer_id, created_at -- fix #3
  HAVING SUM(EXTRACT(MINUTE FROM MAX(created_at)-MIN(created_at))) > 2 -- fix #4
)
SELECT customer_id,
  MAX(created_at) last_order_date,
  MAX(created_at) + ((
    SELECT EXTRACT(MINUTE FROM MAX(created_at)-MIN(created_at)) from s2x
  ) ::text||' minute')::INTERVAL AS nextdate,
  (SELECT AVG(total_price - total_tax) from s2) nextvalue
FROM orders
GROUP BY customer_id

我无法真正运行此查询并为真实测试，但是您明白了。

顺便说一句，sub和s2可以合并为单个CTE。关于subx和s2x。