语句错误对整个列的GROUP BY

Question

我想看到数据库中的所有列(通过选择*)与新列as，以便我可以分析这些列，而无需复制和粘贴在Excel电子表格中。

如何在group by键入而不需要键入每列(超过10列)，通常它会出现错误，如果我使用这个查询:

select   a. *  
min(order_date) over(partition by customer_id) as min_order , 
max(order_date) over(partition by customer_id) as max_order , 
max(create_date) over(partition by customer_id) as max_create , 
sum (count(*)) over (partition by customer_id,
create_date order by create_date desc ) count 
from purchase a
group by a.customer_id

语句错误显示:

ORA-00937: not a single-group group function ORA-02063: preceding line
from OMSRPT_OMS_OBJECTS
00937. 00000 -  "not a single-group group function"
*Cause:
*Action:

谢谢你的帮助

Answer 1

分析函数与聚合函数

• 分析函数和聚合函数不相同
• 有些聚合函数有解析版本，有些没有
• 都处理一组行，但聚合函数总是返回每组1行-与分析函数，结果集不会自动折叠
• 如果你使用分析函数，你不需要GROUP BY子句(在大多数情况下)，因为你已经使用Partition BY
分组行了
• 如果您对一些(1个或多个)非聚合选择使用聚合函数，则必须对每个非聚合选择使用Group By
• 在一个选择中结合分析和聚合函数(就像你做的那样)-以错误结束

让我们假设您的样本数据看起来像这样:

WITH
purchase AS
(
Select 1 "CUSTOMER_ID", To_Date('01.12.2022', 'dd.mm.yyyy') "CREATE_DATE", To_Date('01.12.2022', 'dd.mm.yyyy') "ORDER_DATE", 'AAA' "SOME_COLUMN" From Dual Union All
Select 2 "CUSTOMER_ID", To_Date('02.12.2022', 'dd.mm.yyyy') "CREATE_DATE", To_Date('05.12.2022', 'dd.mm.yyyy') "ORDER_DATE", 'BBB' "SOME_COLUMN" From Dual Union All
Select 2 "CUSTOMER_ID", To_Date('04.12.2022', 'dd.mm.yyyy') "CREATE_DATE", To_Date('10.12.2022', 'dd.mm.yyyy') "ORDER_DATE", 'CCC' "SOME_COLUMN" From Dual Union All
Select 3 "CUSTOMER_ID", To_Date('09.12.2022', 'dd.mm.yyyy') "CREATE_DATE", To_Date('12.12.2022', 'dd.mm.yyyy') "ORDER_DATE", 'DDD' "SOME_COLUMN" From Dual Union All
Select 3 "CUSTOMER_ID", To_Date('09.12.2022', 'dd.mm.yyyy') "CREATE_DATE", To_Date('14.12.2022', 'dd.mm.yyyy') "ORDER_DATE", 'EEE' "SOME_COLUMN" From Dual 
)

如果您将最后一个选择替换为分析版本，并从代码中排除Group By子句-一切都工作正常(只是稍微纠正了代码)

Select   
a.*,  
Min(ORDER_DATE)  OVER(Partition By CUSTOMER_ID) as min_order, 
Max(ORDER_DATE)  OVER(Partition By CUSTOMER_ID) as max_order, 
Max(CREATE_DATE) OVER(Partition By CUSTOMER_ID) as max_create, 
-- the line below is excluded for using combination of SUM() OVER() analytic function with COUNT() aggregate function - you could use Count() OVER() analytic version (2 lines below)
--  Sum(count(*))    OVER(Partition By CUSTOMER_ID, CREATE_DATE order by CREATE_DATE desc ) as sum_count 
Count(*)     OVER(Partition By CUSTOMER_ID, CREATE_DATE order by CREATE_DATE desc ) as a_count  
From purchase a
--Group By a.CUSTOMER_ID        -- in this case you don't need GROUP BY clause

…如果一个表的所有分析函数和所有列都没有GROUP BY子句，结果如下:

<表类>CUSTOMER_IDCREATE_DATEORDER_DATESOME_COLUMNMIN_ORDERMAX_ORDERMAX_CREATEA_COUNTtbody><<tr>101-DEC-2201-DEC-22301-DEC-2201-DEC-2201-DEC-221202-DEC-2205-DEC-22BBB05-DEC-2210-DEC-2204-DEC-221204-DEC-2210-DEC-22CCC05-DEC-2210-DEC-2204-DEC-221309-DEC-2212-DEC-22DDD12-DEC-2214-DEC-2209-DEC-222309-DEC-2214-DEC-22EEE12-DEC-2214-DEC-2209-DEC-222