首页 文章

在窗口中选择第一个和最后一个日期

提问于
浏览 次
1

我正在尝试根据提供的月份和年份在窗口中选择第一个和最后一个日期 .

这是示例数据：

F.rates
| id | c_id | date       | rate |
---------------------------------
| 1  | 1    | 01-01-1991 | 1    |
| 1  | 1    | 15-01-1991 | 0.5  |
| 1  | 1    | 30-01-1991 | 2    |
.................................
| 1  | 1    | 01-11-2014 | 1    |
| 1  | 1    | 15-11-2014 | 0.5  |
| 1  | 1    | 30-11-2014 | 2    |

这是我提出的pgSQL SELECT：

SELECT c_id, first_value(date) OVER w, last_value(date) OVER w FROM F.rates 
WINDOW w AS (PARTITION BY EXTRACT(YEAR FROM date), EXTRACT(MONTH FROM date), c_id 
             ORDER BY date ASC)

这给了我一个非常接近我想要的结果：

| c_id | first_date | last_date  |
----------------------------------
| 1    | 01-01-1991 | 15-01-1991 |
| 1    | 01-01-1991 | 30-01-1991 |
.................................

应该：

| c_id | first_date | last_date  |
----------------------------------
| 1    | 01-01-1991 | 30-01-1991 |
.................................

由于某些原因 last_value(date) 返回窗口中的每条记录 . 这给了我一个想法，我喜欢SQL为它迭代的每一行形成一个新窗口，但不是基于YEAR和MONTH的整个表的多个窗口 .

那么任何人都可以善良并解释我是否错了，我如何达到我想要的结果？

有一个原因我没有使用MAX / MIN而不是GROUP BY子句 . 我的下一步是检索我选择的日期的相关费率，例如：

| c_id | first_date | last_date  | first_rate | last_rate  | avg rate |
-----------------------------------------------------------------------
| 1    | 01-01-1991 | 30-01-1991 | 1          | 2          | 1.1      |
.......................................................................
sql postgresql aggregate-functions window-functions

2 回答

  • 2

    窗口函数不适用于此 . 请改用聚合函数 .

    select 
    c_id, date_trunc('month', date)::date, 
    min(date) first_date, max(date) last_date
from rates
group by c_id, date_trunc('month', date)::date;

    c_id | date_trunc | first_date | last_date
------+------------+------------+------------
    1 | 2014-11-01 | 2014-11-01 | 2014-11-30
    1 | 1991-01-01 | 1991-01-01 | 1991-01-30

    create table rates (
  id integer not null,
  c_id integer not null,
  date date not null,
  rate numeric(2, 1),
  primary key (id, c_id, date)
);

insert into rates values
(1, 1, '1991-01-01', 1),
(1, 1, '1991-01-15', 0.5),
(1, 1, '1991-01-30', 2),
(1, 1, '2014-11-01', 1),
(1, 1, '2014-11-15', 0.5),
(1, 1, '2014-11-30', 2);
    回复于
  • 1

    如果您希望将输出分组为单个（或更少）行，则应使用简单聚合（即 GROUP BY ），如果 avg_rate 足够：

    SELECT c_id, min(date), max(date), avg(rate)
FROM F.rates
GROUP BY c_id, date_trunc('month', date)

    有关PostgreSQL's documentation中窗口函数的更多信息：

    但与常规聚合函数不同，使用窗口函数不会导致行分组为单个输出行 - 行保留其独立的标识 . ...还有另一个与窗口函数相关的重要概念：对于每一行，在其分区中有一组称为窗口框架的行 . 许多（但不是全部）窗口函数仅作用于窗口框架的行，而不是整个分区 . 默认情况下，如果提供ORDER BY，则该帧包含从分区开始到当前行的所有行，以及根据ORDER BY子句等于当前行的所有后续行 . 省略ORDER BY时，默认框架由分区中的所有行组成 . ...有其他方式可以定义窗框...有关详细信息，请参阅第4.2.8节 .

    EDIT ：

    如果要折叠（最小/最大聚合）数据并希望收集比 GROUP BY 中列出的列更多的列，则有2个选择：

    SQL方式

    在子查询中选择最小/最大值，然后将其原始行连接起来（但是这样，您必须处理这样的事实，即min / max-ed列通常不唯一）：

    SELECT c_id,
       min first_date,
       max last_date,
       first.rate first_rate,
       last.rate last_rate,
       avg avg_rate
FROM   (SELECT   c_id, min(date), max(date), avg(rate)
        FROM     F.rates
        GROUP BY c_id, date_trunc('month', date)) agg
JOIN   F.rates first ON agg.c_id = first.c_id AND agg.min = first.date
JOIN   F.rates last  ON agg.c_id = last.c_id  AND agg.max = last.date

    PostgreSQL的DISTINCT ON

    DISTINCT ON通常用于此任务，但高度依赖于排序（一次只能以这种方式搜索1个极值）：

    SELECT   DISTINCT ON (c_id, date_trunc('month', date))
         c_id,
         date first_date,
         rate first_rate
FROM     F.rates
ORDER BY c_id, date

    您可以将此查询与 F.rates 的其他聚合子查询一起加入，但是这一点（如果您确实需要最小值和最大值，在您的情况下甚至是平均值），SQL兼容的方式更适合 .

    回复于

相关问题