查找每个策略的每个月的最新更新记录（另一个字段）

I have a table named a, and other fields as eff_date,policy no.

我有一个名为a的表,其他字段为eff_date,策略号。

Now for each policy, consider all the records, and take out the last updated one (eff_date) from each month.

现在针对每个策略,考虑所有记录,并从每个月中取出最后更新的记录(eff_date)。

So I need the last updated record for each month for each policy. How would I write a query for this?

所以我需要每个政策的每个月的最新更新记录。我该怎么写这个查询?

4 个解决方案

#1

I'm not 100 percent on Teradata syntax, but I believe you're after this:

我不是百分之百的Teradata语法,但我相信你是在这之后:

SELECT policy_no,eff_date
FROM (SELECT policy_no,eff_date, ROW_NUMBER() OVER (PARTITION BY policy no, EXTRACT(YEAR FROM eff_date),EXTRACT(MONTH FROM eff_date) ORDER BY eff_date DESC) as RowRank
      FROM a) as sub
WHERE RowRank = 1

I'm assuming when you say by month you also want to differentiate by year, but if not, just remove the EXTRACT(YEAR FROM eff_date) from the PARTITION BY section.

我假设你按月说你也想按年份进行区分,但如果没有,只需从PARTITION BY部分删除EXTRACT(YEAR FROM eff_date)。

Edit: Update for Teradata syntax.

编辑:Teradata语法的更新。

#2

SELECT * from a 
qualify ROW_NUMBER() OVER (PARTITION BY policy no, EXTRACT(YEAR FROM eff_date),
EXTRACT(MONTH FROM eff_date) ORDER BY eff_date DESC) = 1

#3

The main difficulty, is that the group by needs to be made both the conbination of policy_no, but also the month (extracted from the date). For example:

主要的困难在于,该组需要既可以与policy_no结合,也可以与月份(从日期中提取)结合起来。例如:

In Mysql

    SELECT policy_no,
       month(eff_date),
       year(eff_date),
       max(eff_date)
FROM myTable
GROUP BY policy_no,
         month(eff_date),
         year(eff_date);

Update

I saw derived tables are allowed in teradata. Using a join to a derived table, here is how to access the full rows:

我看到teradata中允许派生表。使用连接到派生表,以下是如何访问完整行:

select * from a,
(SELECT policy_no,
       month(eff_date),
       year(eff_date),
       max(eff_date) as MaxMonthDate
FROM a
GROUP BY policy_no,
         month(eff_date),
         year(eff_date)
 ) as b
where a.policy_no = b.policy_no and
a.eff_date = b.MaxMonthDate;

http://www.sqlfiddle.com/#!2/1f728/5

Update (Using Extract)

select * from a,
    (SELECT a2.policy_no,
           EXTRACT(MONTH FROM a2.eff_date),
           EXTRACT(YEAR FROM  a2.eff_date),
           max(a2.eff_date) as MaxMonthDate
    FROM a as a2
    GROUP BY a2.policy_no,
             EXTRACT(MONTH FROM a2.eff_date),
             EXTRACT(YEAR FROM  a2.eff_date)
     ) as b
    where a.policy_no = b.policy_no and
    a.eff_date = b.MaxMonthDate;

#4

I'm going to suggest looking into Windows Aggregate functions and the QUALIFY statement. I believe the following SQL will work.

我将建议查看Windows聚合函数和QUALIFY语句。我相信以下SQL会起作用。

SELECT Policy_No
     , EXTRACT(MONTH FROM Eff_Date) AS Eff_Month_
     , Eff_Date
  FROM TableA
QUALIFY ROW_NUMBER() OVER (PARTITION BY Policy_No, EXTRACT(MONTH FROM Eff_Date) 
                           ORDER BY Eff_Date DESC) = 1;