主页 / dba / 问题 / 184356
Accepted
daniel9x
Asked: 2017-08-26 06:33:37 +0800 CST

Postgres Order By 但只有特定类型的最新

  • 772

假设我有一个包含三列的 PET 表：名称、类型和日期。我想按日期对三列进行排序，但只显示特定类型的最新一列（本例中为“狗”），其余的全部显示。例如，我的原始数据列表：

Name        Type        Date
Nemo        Fish        June 1, 2016
Fido        Dog         January 1, 2016
Felix       Dog         February 1, 2016
Whiskers    Cat         April 1, 2016
Marlin      Fish        August 1, 2016
Shifu       Cat         March 3, 2016

应用查询时看起来像这样。

Felix       Dog         February 1, 2016
Shifu       Cat         March 3, 2016
Whiskers    Cat         April 1, 2016
Nemo        Fish        June 1, 2016
Marlin      Fish        August 1, 2016

我敢肯定有多种方法可以做到这一点，但从效率的角度来看，只有几种方法是最佳的。

postgresql performance

2 个回答

  1. Best Answer

    解决方案 1

    CREATE TABLE pet ( name text, type text, date date);

INSERT INTO pet VALUES
 ('Nemo', 'Fish', 'June 1, 2016'),
 ('Fido', 'Dog', 'January 1, 2016'),
 ('Felix', 'Dog', 'February 1, 2016'),
 ('Whiskers', 'Cat', 'April 1, 2016'),
 ('Marlin', 'Fish', 'August 1, 2016'),
 ('Shifu', 'Cat', 'March 3, 2016');

    使用特定于 PostgreSQL 的 DISTINCT ON 子句，我们可以在单个表扫描中执行此操作：

    SELECT name, type, date
FROM (
  SELECT DISTINCT ON (
    type,
    CASE WHEN type<>'Dog' THEN date END
  )
  name, type, date
  FROM pet
  ORDER BY
    type,
    CASE WHEN type<>'Dog' THEN date END,
    date DESC
) AS subq1
ORDER BY date;

    方案二

    使用更多与 ANSI 兼容的 SQL，例如UNION和 LIMIT。它可以在 MySQL、DB2 和其他一些平台上运行。类似的解决方案可以在 Oracle 上完成，只需将 LIMIT 替换为 ROWNUM。

    (
  SELECT * FROM pet
  WHERE type = 'Dog'
  ORDER BY date DESC
  LIMIT 1
) UNION ALL (
  SELECT * FROM pet
  WHERE type <> 'Dog'
)
ORDER BY date;

    性能说明

    对于小表（少于 1000000 行），任何解决方案都适用。DISTINCT ON稍微快一点：

    Iterations: 100000
Query:   /*q1*/ SELECT name, type, date FROM ( SELECT DISTINCT ON ( type, CASE WHEN type<>'Dog' THEN date END ) name, type, date FROM pet ORDER BY type, CASE WHEN type<>'Dog' THEN date END, date DESC ) AS subq1 ORDER BY date; 
Time:    24.834 s (31%)
Average: 0.248 ms
Rows:    500000
Winner:  80914 times (80%)
Query:   /*q2*/ ( SELECT * FROM pet WHERE type = 'Dog' ORDER BY date DESC LIMIT 1 ) UNION ALL ( SELECT * FROM pet WHERE type <> 'Dog' ) ORDER BY date; 
Time:    26.778 s (33%)
Average: 0.268 ms
Rows:    500000
Winner:  12881 times (12%)
Query:   /*q3*/ select name, type, date from ( select pet.*, case when type = 'Dog' then rank() over (partition by type order by date desc) else 1 end as rnk from pet ) as x where rnk = 1 order by date; 
Time:    27.490 s (34%)
Average: 0.275 ms
Rows:    500000
Winner:  6205 times (6%)

    对于“大数据”，确保您的查询只进行一次索引表扫描。

    • 2

  2. 作为对@filiprems 解决方案的补充，这里有一个使用条件窗口函数的解决方案：

    test=# select name, type, date from (
           select pet.*, case when type <> 'Dog' 
                              then 1 
                              else rank() over (partition by type
                                                order by date desc) end as rnk 
           from pet
       ) as x 
       where rnk = 1 
       order by date;

name   | type |    date    
----------+------+------------
Felix    | Dog  | 2016-02-01
Shifu    | Cat  | 2016-03-03
Whiskers | Cat  | 2016-04-01
Nemo     | Fish | 2016-06-01
Marlin   | Fish | 2016-08-01
(5 rows)

    我不指望它在任何方面都变得更好，它只是展示了另一种做事方式。

    • 1

相关问题