获取最近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

table中目前没有任何UNIQUE限制，但是我们有一项服务，每天只插入一个计数。(name, sid, date)``book

我必须获得基于的结果store.id。当我传递ID时，应使用书名，出售日期和出售份数生成报告。

所需的输出：

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

Answer 1

这看起来并不令人怀疑，但这 是个难题 。

假设条件

• 你的数目是integer。
• 表格中的所有列均已定义NOT NULL。
• 该(name, sid, date)表在表中是唯一的book。您应该有一个UNIQUE约束，最好（出于性能考虑）按 以下 顺序排列各列：

  UNIQUE(sid, date, name)
  

这将自动提供性能所需的索引。（否则创建一个。）请参阅：

* [多列索引和性能](https://dba.stackexchange.com/q/33196/3684)
* [复合索引对第一个字段的查询是否也有用？](https://dba.stackexchange.com/q/27481/3684)

crosstab() 询问

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

• PostgreSQL交叉表查询

基本查询

您需要首先正确解决这些问题。

最近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';

然后，您的呼叫将变为：

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
**, "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")** ;

困难在于提炼动态列名。手动组装查询字符串，或者（更确切地说）让此函数为您完成此操作：

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);

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