按客户和月份显示最新值的行 [英] Row with latest value by customer and month

问题描述

我有一个表，可跟踪客户资料的变化. 这是简化版本:

I have a table that keeps track of changes in customer profiles. Here's a simplified version:

CREATE TABLE HISTORY (
    CUSTOMER_ID NUMBER(9,0), 
    DATE_CHANGED DATE, 
    ACCOUNT_TYPE VARCHAR2(20),

    CONSTRAINT HISTORY_PK PRIMARY KEY (CUSTOMER_ID, DATE_CHANGED)
);

INSERT INTO HISTORY (CUSTOMER_ID, DATE_CHANGED, ACCOUNT_TYPE) VALUES (200, TO_DATE('05/01/2013 00:00:00','DD/MM/RRRR HH24:MI:SS'), 'Premium');
INSERT INTO HISTORY (CUSTOMER_ID, DATE_CHANGED, ACCOUNT_TYPE) VALUES (300, TO_DATE('17/02/2013 00:00:00','DD/MM/RRRR HH24:MI:SS'), 'Free');
INSERT INTO HISTORY (CUSTOMER_ID, DATE_CHANGED, ACCOUNT_TYPE) VALUES (100, TO_DATE('05/03/2013 00:00:00','DD/MM/RRRR HH24:MI:SS'), 'Free');
INSERT INTO HISTORY (CUSTOMER_ID, DATE_CHANGED, ACCOUNT_TYPE) VALUES (100, TO_DATE('12/03/2013 00:00:00','DD/MM/RRRR HH24:MI:SS'), 'Standard');
INSERT INTO HISTORY (CUSTOMER_ID, DATE_CHANGED, ACCOUNT_TYPE) VALUES (200, TO_DATE('22/03/2013 00:00:00','DD/MM/RRRR HH24:MI:SS'), 'Standard');
INSERT INTO HISTORY (CUSTOMER_ID, DATE_CHANGED, ACCOUNT_TYPE) VALUES (100, TO_DATE('29/03/2013 00:00:00','DD/MM/RRRR HH24:MI:SS'), 'Premium');

该数据由第三方维护.我的最终目标是在给定的时间范围内按帐户类型和月份获得客户总数，但现在，我想从一个简单的事情开始-显示记录了更改的每个月/客户组合的最新帐户类型:

That data is maintained by a third-party. My ultimate goal is to obtain a sum of customers per account type and month for a given timespan but, by now, I'd like to start with something simpler—display the latest account type for each month/customer combination where there are changes recorded:

YEAR MONTH CUSTOMER_ID ACCOUNT_TYPE
==== ===== =========== ============
2013     1         200 Premium
2013     2         300 Free
2013     3         100 Premium
2013     3         200 Standard

在这里，客户100在3月进行了3次更改；我们显示高级"，因为它具有3月内的最新日期.

Here, customer 100 has made three changes on March; we display "Premium" because it has the latest date within March.

用于获取所有行的查询是这样的:

The query to obtain all rows would be this:

SELECT EXTRACT(YEAR FROM DATE_CHANGED) AS YEAR,
EXTRACT(MONTH FROM DATE_CHANGED) AS MONTH,
CUSTOMER_ID, ACCOUNT_TYPE
FROM HISTORY
ORDER BY YEAR, MONTH, CUSTOMER_ID, DATE_CHANGED

是否可以使用聚合函数过滤掉不需要的行?使用分析函数是否更有意义?

Is it possible to filter out unwanted rows using aggregate functions? Does it make more sense to use analytic functions?

(无论哪种情况，适当的功能是什么?)

(And, in either case, what would be the adequate function?)

编辑:有人要求我提供一个不需要的行的示例. 3月，客户100有3行:

I've been asked for an example of unwanted rows. There're 3 rows for customer 100 on March:

'05/03/2013 00:00:00', 'Free'
'12/03/2013 00:00:00', 'Standard'
'29/03/2013 00:00:00', 'Premium'

不需要的行是'Free'和'Standard'，因为它们不是当月的最新行.

Unwanted rows are 'Free' and 'Standard' because they aren't the latest in the month.

推荐答案

SELECT YEAR
      ,MONTH
      ,customer_id
      ,max(ACCOUNT_TYPE) keep(dense_rank FIRST ORDER BY date_changed DESC) LAST_ACC
 FROM (
  SELECT EXTRACT(YEAR FROM DATE_CHANGED) AS YEAR,
         EXTRACT(MONTH FROM DATE_CHANGED) AS MONTH,
         CUSTOMER_ID,
         date_changed,
         account_type
   FROM HISTORY
  )
GROUP BY YEAR, MONTH, customer_id
ORDER BY YEAR, MONTH, CUSTOMER_ID


| YEAR | MONTH | CUSTOMER_ID | LAST_ACC |
-----------------------------------------
| 2013 |     1 |         200 |  Premium |
| 2013 |     2 |         300 |     Free |
| 2013 |     3 |         100 |  Premium |
| 2013 |     3 |         200 | Standard |

http://sqlfiddle.com/#!4/e493a/15

这篇关于按客户和月份显示最新值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！