sql-oracle-选择特定组/sku的第一条记录和最后一条记录

问题描述

我一直在努力寻求答案，但没有成功.

I've been trying to come up for answer but no success.

需要获取:首次购买的价格和最后一次购买的价格，并按SKU分组.

Need to get: price for the first purchase and price for the last purchase and grouped by SKU.

查询结果应如下:

sku      first_purchase_price      Last_purchase_price
BC123    3.09                      6.68
QERT1    9.09                      13.23

我的查询

SELECT sku,PRICE,MAX(purchase_DATE),MIN (purchase_DATE)
FROM store
ORDER By sku

继续获取:

SQL Error: ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"

或

SQL Error: ORA-00937: not a single-group group function
00937. 00000 -  "not a single-group group function"

非常感谢您的帮助.

SKU     TRANSAC_ID  purchase_DATE      PRICE
----------------------------------------------
BC123   CHI0018089  21-OCT-09          6.98
BC123   CHI0031199  11-MAR-13          6.68
BC123   NAP1000890  22-JAN-08          3.09
BC123   NAP1011123  21-DEC-11          89.9
QQQ789  NAP1000891  22-JAN-08          4.01
QERT1   JOL0400090  8-MAR-12           13.23
QERT1   NAP1000990  22-FEB-08          9.09
QERT1   NAP1001890  28-FEB-09          2.09
WW000   CHI0031208  11-MAR-13          200.01
WW000   CHI0031298  11-MAR-13          200.01
YZV11   JOL0200080  10-OCT-06          230.23
YZV11   AUR0700979  14-APR-13          6.68
YZV11   CHI0018189  03-OCT-09          556.98
YZV11   JOL0300080  10-MAR-11          300

推荐答案

您可以使用第一个/最后一个将函数聚合到简化此类查询.

You can make use of FIRST/LAST aggregate functions tosimplify this type of query.

SQL小提琴

查询:

select
        sku,
        max(price) keep (dense_rank first order by purchase_date) first_purchase_price,
        max(price) keep (dense_rank last order by purchase_date) last_purchase_price
from
        store
group by
        sku;

结果 :

Results:

|    SKU | FIRST_PURCHASE_PRICE | LAST_PURCHASE_PRICE |
|--------|----------------------|---------------------|
|  BC123 |                 3.09 |                6.68 |
|  QERT1 |                 9.09 |               13.23 |
| QQQ789 |                 4.01 |                4.01 |
|  WW000 |               200.01 |              200.01 |
|  YZV11 |               230.23 |                6.68 |

这篇关于sql-oracle-选择特定组/sku的第一条记录和最后一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！