获取最近10个日期的行 [英] Get rows for the last 10 dates

问题描述

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

I have a scenario in a Postgres 9.3 database where I have to get the last 10 dates when books were sold. Consider below example:

   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

当前在（名称，sid，日期） UNIQUE 约束c>在表 book 中，但是我们有一项服务，每天只插入一个计数。

There is currently no UNIQUE constraint on (name, sid, date) in table book, but we have a service in place that inserts only one count per day.

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

I have to get results based on store.id. When I pass the ID, the report should be generated with bookname, sold date, and the count of sold copies.

所需的输出：

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

推荐答案

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

This looks unsuspicious, but it's a hell of a question.

• 您的计数为整数。


• 其中的所有列餐桌书定义为 NOT NULL 。



• 组合（名称，sid，日期）在表 book 中是唯一的。您应该有一个 UNIQUE 约束，最好（为了提高性能）按 this 顺序排列各列：

• Your counts are integer.
• All columns in table book are defined NOT NULL.

• The composite (name, sid, date) is unique in table book. You should have a UNIQUE constraint, preferably (for performance) with columns in this order:

UNIQUE(sid, date, name)

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

This provides the index needed for performance automatically. (Else create one.) See:

• 多列索引和性能


• 复合索引是否也适合

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

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

To get top performance and short query strings (especially if you run this query often) I suggest the additional module tablefunc providing various crosstab() functions. Basic instructions:

• PostgreSQL交叉表查询

• PostgreSQL Crosstab Query

您需要首先正确处理这些问题。

You need to get these right first.

最近10天：

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

使用窗口函数 dense_rank（） ：

Numbers for last 10 days using the window function 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;

（此查询中不包括实际日期。）

(Not including actual dates in this query.)

输出列的列名（用于完整解决方案）：

Column names for output columns (for full solution):

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;

具有静态列名称的简单结果

这可能对您足够好-但我们在结果中看不到实际日期：

Simple result with static column names

This may be good enough for you - but we don't see actual dates in the result:

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函数，简化一下事情：

For repeated use I suggest you create this (very fast) generic C function for 10 integer columns once, to simplify things a bit:

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

此相关答案的详细信息：

Details in this related answer:

• 在PostgreSQL中动态生成用于交叉表的列

• 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!

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

您的实际问题更复杂，您还需要动态列名。

对于给定的表，结果查询如下所示：

Full solution with dynamic column names

Your actual question is more complicated, you also want dynamic column names.
For a given table, the resulting query could look like this then:

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

困难在于提炼动态列名。要么手工组装查询字符串，要么（更确切地说）让该函数为您完成：

The difficulty is to distill dynamic column names. Either assemble the query string by hand, or (much rather) let this function do it for you:

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

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

This generates the desired query, which you execute in turn.

db<>小提琴这里

这篇关于获取最近10个日期的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！