SQL:获取最近一个月的前N名和这些月的历史记录

Question

我想找到最近一个周期的前N个名称(值最高的名称(。对于这些名字，我想获得历史价值。

根据这个问题，为每个类别选择前10条记录

我尝试过以下几种：

base_table (period, name, value)
CREATE VIEW TOP3 AS
SELECT DISTINCT a.*, COUNT(*) as rank
FROM base_table AS a
LEFT JOIN base_table AS a2 
ON a.period = a2.period and a.value <= a2.value
GROUP BY a.value
HAVING COUNT(*) <= 3
ORDER BY period, rank;

这让我在每节课上都获得了前三名。

period | name | value | rank
092020   Joe    100      1
092020   Jane   90       2
092020   Doe    80       3
082020   Jan    100      1
082020   Doc    99       2
082020   Pete   98       3
.
.
.

但不是预期的结果。

假设这些是最近一个月的前三名：

period | name | value | rank
092020   Joe    100      1
092020   Jane   90       2
092020   Doe    80       3

现在我想了解这些的历史，尽管它们在上个月的前三名之外，就像这样：

period | name | value | rank
092020   Joe    100      1
092020   Jane   90       2
092020   Doe    80       3
082020   Joe    95       4
082020   Jane   94       5
082020   Doe    7        50
.        .      .        .
.        .      .        .
.        .      .        .
092019  Joe     42       20
092019  Doe     34       35
092019  Jane    1        100

仅供参考，我在SAS工作，使用PROC SQL，所以像CTE、窗口函数和其他东西之类的细节对我来说是不可用的。

Answer 1

你能用子查询试试吗？如果没有，你可以创建一个视图，而不是用一些row_number((/rank函数来加入

SELECT b.period, b.name, b.value
FROM base_table b
INNER JOIN
(  
-- subquery to get last period 
SELECT TOP 3 name, period, value
FROM base_table
WHERE period = (SELECT MAX(period) FROM base_table)
ORDER BY 3 DESC
) AS s ON s.name = b.name -- join them

Answer 2

您可以使用JOIN:

SELECT a.*, ar.rank
FROM base_table a JOIN
(SELECT a.value, COUNT(*) as rank
FROM base_table a JOIN
base_table a2 
ON a.period = a2.period and a.value <= a2.value JOIN
(SELECT max(a3.period) as max_period
FROM base_table a3
) a3
ON a3.max_period = period
GROUP BY a.value
HAVING COUNT(*) <= 3
) ar
ON ar.value = a.value
ORDER BY period, rank;

我怀疑使用data步骤会更简单，至少在分配秩时是这样。

Answer 3

不确定为什么要使用SQL。下面是一个使用PROC SQL的OUTOBS选项的方法。

首先，让我们制作一些实际的测试数据：

data have ;
input period :ddmmyy. name $ value rank ;
format period yymm7. ;
cards;
01092020   Joe    100      1
01092020   Jane   90       2
01092020   Doe    80       3
01082020   Joe    95       4
01082020   Jane   94       5
01082020   Doe    7        50
01092019  Joe     42       20
01092019  Doe     34       35
01092019  Jane    1        100
;

现在，让我们将N设置为一个宏变量，使其更加灵活。由于示例数据只有3个名称，因此我们使用2的N。

%let n=2;

因此，首先找到最近(MAX(时段的前N。

proc sql;
reset outobs=&n;
create table top&n as select name
from have 
having period=max(period)
order by value desc
;

现在将OUTOBS重置为MAX，并使用TOPn列表从原始数据集中提取这些名称的所有数据。

reset outobs=max;
create table want as 
select * from have
where name in (select name from top&n)
;
quit;

让我们看看结果：

proc print data=want;
run;

即：

Obs     period    name    value    rank
1     2020M09    Joe      100        1
2     2020M09    Jane      90        2
3     2020M08    Joe       95        4
4     2020M08    Jane      94        5
5     2019M09    Joe       42       20
6     2019M09    Jane       1      100