sql - 获取最近 10 个日期的行

Question

我在 Postgres 9.3 数据库中有一个场景，我必须获取最近 10 本书售出的日期。考虑以下示例:

   Store                Book
 ----------        ----------------------
 Id  Name          Id Name Sid Count Date
 1   ABC           1  XYZ  1   20    11/11/2015
 2   DEF           2  JHG  1   10    11/11/2015
                   3  UYH  1   10    15/11/2015
                   4  TRE  1   50    17/11/2015

book 表中的(name, sid, date) 目前没有UNIQUE 约束，但是我们有一个服务每天只插入一个计数。

我必须根据 store.id 获取结果。当我通过 ID 时，应该会生成包含书名、售出日期和售出份数的报告。

期望的输出:

 BookName  11/11/2015 15/11/2015  17/11/2015
 XYZ       20         --          --
 JHG       10         --          --
 UYH       --         10          --
 TRE       --         --          50

Answer 1

这看起来并不可疑，但它是一个 hell 般的问题。

假设

• 您的计数是整数。
• table book 中的所有列都定义为 NOT NULL。

• 复合 (name, sid, date) 在表 book 中是唯一的。您应该有一个 UNIQUE 约束，最好(为了性能)列按这个顺序排列:

  UNIQUE(sid, date, name)
  

  这会自动提供性能所需的索引。 (否则创建一个。)参见:

  • Multicolumn index and performance
  • Is a composite index also good for queries on the first field?

crosstab() 查询

为了获得最佳性能和较短的查询字符串(尤其是如果您经常运行此查询)，我建议使用附加模块 tablefunc 提供各种 crosstab() 函数。 基本说明:

• PostgreSQL Crosstab Query

基本查询

你需要先把这些做好。

最近 10 天:

SELECT DISTINCT date
FROM   book
WHERE  sid = 1
ORDER  BY date DESC
LIMIT  10;

最近 10 天的数字使用窗口函数 dense_rank() :

SELECT *
FROM  (
   SELECT name
        , dense_rank() OVER (ORDER BY date DESC) AS date_rnk
        , count
   FROM   book
   WHERE  sid = 1
   ) sub
WHERE  date_rnk < 11
ORDER  BY name, date_rnk DESC;

(不包括此查询中的实际日期。)

输出列的列名(完整解决方案):

SELECT 'bookname, "' || string_agg(to_char(date, 'DD/MM/YYYY'), '", "' ORDER BY date) || '"'
FROM  (
   SELECT DISTINCT date
   FROM   book
   WHERE  sid = 1
   ORDER  BY date DESC
   LIMIT  10
   ) sub;

使用静态列名的简单结果

这对您来说可能已经足够了 - 但我们在结果中看不到实际日期:

SELECT * FROM crosstab(
  'SELECT *
   FROM  (
      SELECT name
           , dense_rank() OVER (ORDER BY date DESC) AS date_rnk
           , count
      FROM   book
      WHERE  sid = 1
      ) sub
   WHERE  date_rnk < 11
   ORDER  BY name, date_rnk DESC'
, 'SELECT generate_series(10, 1, -1)'
 ) AS (bookname text
     , date1 int, date2 int, date3 int, date4 int, date5 int
     , date6 int, date7 int, date8 int, date9 int, date10 int);

为了重复使用，我建议您一次为 10 个整数列创建这个(非常快的)通用 C 函数，以稍微简化一下:

CREATE OR REPLACE FUNCTION crosstab_int10(text, text)
  RETURNS TABLE (bookname text
               , date1 int, date2 int, date3 int, date4 int, date5 int
               , date6 int, date7 int, date8 int, date9 int, date10 int)
  LANGUAGE C STABLE STRICT AS
'$libdir/tablefunc','crosstab_hash';

此相关答案中的详细信息:

• Dynamically generate columns for crosstab in PostgreSQL

然后你的电话变成:

SELECT * FROM crosstab(
  'SELECT *
   FROM  (
      SELECT name
           , dense_rank() OVER (ORDER BY date DESC) AS date_rnk
           , count
      FROM   book
      WHERE  sid = 1
      ) sub
   WHERE  date_rnk < 11
   ORDER  BY name, date_rnk DESC'
, 'SELECT generate_series(10, 1, -1)'
 );  -- no column definition list required!

具有动态列名的完整解决方案

您的实际问题更复杂，您还需要动态列名。
对于给定的表，结果查询可能如下所示:

SELECT * FROM crosstab_int10(
  'SELECT *
   FROM  (
      SELECT name
           , dense_rank() OVER (ORDER BY date DESC) AS date_rnk
           , count
      FROM   book
      WHERE  sid = 1
      ) sub
   WHERE  date_rnk < 11
   ORDER  BY name, date_rnk DESC'
, 'SELECT generate_series(10, 1, -1)'
   ) AS t(bookname
<b>        , "04/11/2015", "05/11/2015", "06/11/2015", "07/11/2015", "08/11/2015"
        , "09/11/2015", "10/11/2015", "11/11/2015", "15/11/2015", "17/11/2015")</b>;

困难在于提取动态列名。要么手动组装查询字符串，要么(更确切地说)让这个函数为你做:

CREATE OR REPLACE FUNCTION f_generate_date10_sql(_sid int = 1) 
  RETURNS text
  LANGUAGE sql AS
$func$
SELECT format(
 $$SELECT * FROM crosstab_int10(
  'SELECT *
   FROM  (
      SELECT name
           , dense_rank() OVER (ORDER BY date DESC) AS date_rnk
           , count
      FROM   book
      WHERE  sid = %1$s
      ) sub
   WHERE  date_rnk < 11
   ORDER  BY name, date_rnk DESC'
, 'SELECT generate_series(10, 1, -1)'
   ) AS ct(bookname, "$$
|| string_agg(to_char(date, 'DD/MM/YYYY'), '", "' ORDER BY date) || '")'
 , _sid)
FROM  (
   SELECT DISTINCT date
   FROM   book
   WHERE  sid = 1
   ORDER  BY date DESC
   LIMIT  10
   ) sub
$func$;

调用:

SELECT f_generate_date10_sql(1);

这将生成所需的查询，您将依次执行该查询。

db<> fiddle here