时间戳

首页 > 解决方案 > 删除相隔不到 2 分钟的订单时出现 PostgreSQL 错误 SQL 错误 [42803]：错误：WHERE 中不允许使用聚合函数

sql - 删除相隔不到 2 分钟的订单时出现 PostgreSQL 错误 SQL 错误 [42803]：错误：WHERE 中不允许使用聚合函数

问题描述

删除相隔不到 2 分钟的订单时出现以下错误，但我无法使用HAVING，因为子查询中没有 a GROUP BY。

我是否正确地处理了这个问题，我应该GROUP做些什么来完成这项工作吗？

SQL 错误 [42803]：错误：WHERE 中不允许使用聚合函数

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

标签： sqlpostgresql

解决方案

好的，我使用 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。

推荐阅读