如何使用PostgreSQL简化Python代码

Python是一种编程语言，可让您快速工作并更有效地集成系统，而PostgreSQL是世界上最先进的开源数据库。 这两个人在一起工作得很好。 本文介绍解决简单问题时如何充分利用PostgreSQL（psql）。 尽管将Python代码引发问题很诱人，但它并不总是最佳选择。

SQL具有相当大的处理能力，将SQL集成到您的工作流中通常意味着编写更少的代码行。 正如Edsger Dijkstra所说的那样，代码行就是花费的行：

放心的幻想充斥着这种做法，即程序就像其他任何设备一样，唯一的区别是程序的制造可能需要一种新型的工匠，即。 程序员。 从那里开始，按照“每月产生的代码行数”衡量“程序员生产率”仅一步之遥。 这是一个非常昂贵的度量单位，因为它鼓励编写平淡的代码，但是今天，即使从纯粹的业务角度来看，我对它的愚蠢程度也不再感兴趣。 我今天的观点是，如果我们希望对代码行进行计数，我们不应将其视为“生产的行”，而应视为“花费的行”：当前的传统观点是如此愚蠢，以至于将计数视为错误的一面。分类帐。
— Dijkstra， “关于真正教授计算科学的残酷性”。

通过使用SQL，您可以编写更少的代码，因此可以在更少的时间内编写应用程序。

一个简单的用例

为了检查PostgreSQL和Python如何协同工作，我们将使用纽约证券交易所（NYSE）的“ NYSE上市的纽约证券交易所每日交易量”数据集。 要下载数据，请访问“ 事实与数字”交互式查看器 ，单击“ 市场活动” ，然后单击“列出的NYSE”中的 “ 每日NYSE小组交易量” 。 然后，点击顶部的“ Excel”符号（实际上是一个使用Tab作为分隔符的CSV文件），将“ factbook.xls”文件保存到您的计算机中，打开并删除标题，然后将其加载到PostgreSQL表。

加载数据集

数据如下所示。 它包含用逗号分隔的数千个和美元符号，因此我们无法轻松地将数字作为数字处理。

   
   

    
    

     
     2010    1/4/2010        1,425,504,460   4,628,115       $38,495,460,645
     
     

2010    1/5/2010        1,754,011,750   5,394,016       $43,932,043,406
     
     

2010    1/6/2010        1,655,507,953   5,494,460       $43,816,749,660
     
     

2010    1/7/2010        1,797,810,789   5,674,297       $44,104,237,184
    
    
   
   

要更改此设置，我们可以创建一个临时表定义，一旦数据被加载，由于使用了alter table命令，该数据将被转换为适当SQL数据类型。

   
   

    
    
 
     
     BEGIN ;
     
     


     
     


     
     CREATE 
     
     TABLE factbook
     
     

 
     
     ( 
     
     

   
     
     YEAR    
     
     INT 
     
     , 
     
     

   
     
     DATE    
     
     DATE 
     
     , 
     
     

   shares  text 
     
     , 
     
     

   trades  text 
     
     , 
     
     

   dollars text
     
     

 
     
     ) ;
     
     


     
     

\copy factbook 
     
     FROM 
     
     'factbook.csv' 
     
     WITH delimiter E 
     
     ' \t ' 
     
     NULL 
     
     '' 
     
     


     
     


     
     ALTER 
     
     TABLE factbook
     
     

   
     
     ALTER shares
     
     

    
     
     TYPE 
     
     BIGINT 
     
     

   
     
     USING 
     
     REPLACE 
     
     ( shares 
     
     , 
     
     ',' 
     
     , 
     
     '' 
     
     ) :: 
     
     BIGINT 
     
     , 
     
     


     
     

   
     
     ALTER trades
     
     

    
     
     TYPE 
     
     BIGINT 
     
     

   
     
     USING 
     
     REPLACE 
     
     ( trades 
     
     , 
     
     ',' 
     
     , 
     
     '' 
     
     ) :: 
     
     BIGINT 
     
     , 
     
     

   
     
     

   
     
     ALTER dollars
     
     

    
     
     TYPE 
     
     BIGINT 
     
     

   
     
     USING 
     
     SUBSTRING 
     
     ( 
     
     REPLACE 
     
     ( dollars 
     
     , 
     
     ',' 
     
     , 
     
     '' 
     
     ) 
     
     FROM 
     
     2 
     
     ) :: 
     
     NUMERIC ;
     
     


     
     

commit;
    
    
   
    

copy功能可将数据从CSV文件流式传输到我们的表格中。 \copy变体是特定于psql的命令，它启动客户端/服务器的数据流传输，读取本地文件并通过任何已建立的PostgreSQL连接发送其内容。

应用程序代码和SQL

该文件中有很多数据，因此在此示例中使用2017年2月的数据。 以下查询列出了2017年2月的所有条目：

   
   

    
    

     
     \ 
     
     SET 
     
     START 
     
     '2017-02-01' 
     
     


     
     

  
     
     SELECT 
     
     DATE 
     
     , 
     
     

         to_char 
     
     ( shares 
     
     , 
     
     '99G999G999G999' 
     
     ) 
     
     AS shares 
     
     , 
     
     

         to_char 
     
     ( trades 
     
     , 
     
     '99G999G999' 
     
     ) 
     
     AS trades 
     
     , 
     
     

         to_char 
     
     ( dollars 
     
     , 
     
     'L99G999G999G999' 
     
     ) 
     
     AS dollars
     
     

    
     
     FROM factbook
     
     

   
     
     WHERE 
     
     DATE 
     
     >= 
     
     DATE : 
     
     'start' 
     
     

     
     
     AND 
     
     DATE  
     
     < 
     
     DATE : 
     
     'start' 
     
     + 
     
     INTERVAL 
     
     '1 month' 
     
     


     
     ORDER 
     
     BY 
     
     DATE ;
    
    
   
   

我们使用psql应用程序来运行此查询，并且psql支持使用变量。 \set命令将'2017-02-01'值设置为变量start ，然后我们可以使用表达式:'start'重用该变量。

编写date :'start'等同于date '2017-02-01'这在PostgreSQL中称为修饰的文字表达式。 这使我们能够设置文字值的数据类型，以便PostgreSQL查询解析器不必从上下文中猜测或推断出它。

此SQL查询还使用interval数据类型来计算月末，在我们的示例中，月末当然是2月的最后一天。 将interval值1 month添加到该1 month的第一天将为我们提供下个月的第一天，因此我们使用“小于”（ < ）严格运算符将这一天从结果集中排除。

to_char()函数（在PostgreSQL文档的“ 数据类型格式化函数”部分中提供了文档）将数字转换为其文本表示形式，并对转换进行了详细控制。 该格式由模板模式组成。 我们将使用以下模式：

• 具有指定位数的值
• L ：货币符号（使用语言环境）
• G ：组分隔符（使用语言环境）

可以使用其他用于数字格式设置的模板模式-请参见PostgreSQL文档以供参考。

这是查询的结果：

   
   

    
    

     
         date    │     shares      │   trades    │     dollars      
     
     

════════════╪═════════════════╪═════════════╪══════════════════
     
     

 2017-02-01 │   1,161,001,502 │   5,217,859 │ $ 44,660,060,305
     
     

 2017-02-02 │   1,128,144,760 │   4,586,343 │ $ 43,276,102,903
     
     

 2017-02-03 │   1,084,735,476 │   4,396,485 │ $ 42,801,562,275
     
     

 2017-02-06 │     954,533,086 │   3,817,270 │ $ 37,300,908,120
     
     

 2017-02-07 │   1,037,660,897 │   4,220,252 │ $ 39,754,062,721
     
     

 2017-02-08 │   1,100,076,176 │   4,410,966 │ $ 40,491,648,732
     
     

 2017-02-09 │   1,081,638,761 │   4,462,009 │ $ 40,169,585,511
     
     

 2017-02-10 │   1,021,379,481 │   4,028,745 │ $ 38,347,515,768
     
     

 2017-02-13 │   1,020,482,007 │   3,963,509 │ $ 38,745,317,913
     
     

 2017-02-14 │   1,041,009,698 │   4,299,974 │ $ 40,737,106,101
     
     

 2017-02-15 │   1,120,119,333 │   4,424,251 │ $ 43,802,653,477
     
     

 2017-02-16 │   1,091,339,672 │   4,461,548 │ $ 41,956,691,405
     
     

 2017-02-17 │   1,160,693,221 │   4,132,233 │ $ 48,862,504,551
     
     

 2017-02-21 │   1,103,777,644 │   4,323,282 │ $ 44,416,927,777
     
     

 2017-02-22 │   1,064,236,648 │   4,169,982 │ $ 41,137,731,714
     
     

 2017-02-23 │   1,192,772,644 │   4,839,887 │ $ 44,254,446,593
     
     

 2017-02-24 │   1,187,320,171 │   4,656,770 │ $ 45,229,398,830
     
     

 2017-02-27 │   1,132,693,382 │   4,243,911 │ $ 43,613,734,358
     
     

 2017-02-28 │   1,455,597,403 │   4,789,769 │ $ 57,874,495,227
     
     

(19 rows)
    
    
   
   

该数据集仅在2017年2月（纽约证券交易所开放日）的19天提供数据。 如果我们要显示每个日历日的条目并用匹配的数据或零数字填写缺少的日期怎么办？

这是一个典型的Python实现：

   
   

    
    
 
     
     #! /usr/bin/env python3 
     
     


     
     


     
     import 
     
     sys 
     
     


     
     import psycopg2
     
     


     
     import psycopg2. 
     
     extras 
     
     


     
     from 
     
     calendar 
     
     import Calendar
     
     


     
     

CONNSTRING 
     
     = 
     
     "dbname=yesql application_name=factbook" 
     
     


     
     


     
     


     
     def fetch_month_data 
     
     ( year 
     
     , month 
     
     ) :
     
     

    
     
     "Fetch a month of data from the database" 
     
     

    date 
     
     = 
     
     "%d-%02d-01" % 
     
     ( year 
     
     , month 
     
     ) 
     
     

    sql 
     
     = 
     
     """

  select date, shares, trades, dollars

    from factbook

   where date >= date %s

     and date  < date %s + interval '1 month'

order by date;

""" 
     
     

    pgconn 
     
     = psycopg2. 
     
     connect 
     
     ( CONNSTRING 
     
     ) 
     
     

    curs 
     
     = pgconn. 
     
     cursor 
     
     ( 
     
     ) 
     
     

    curs. 
     
     execute 
     
     ( sql 
     
     , 
     
     ( date 
     
     , date 
     
     ) 
     
     ) 
     
     


     
     

    res 
     
     = 
     
     { 
     
     } 
     
     

    
     
     for 
     
     ( date 
     
     , shares 
     
     , trades 
     
     , dollars 
     
     ) 
     
     in curs. 
     
     fetchall 
     
     ( 
     
     ) :
     
     

        res 
     
     [ date 
     
     ] 
     
     = 
     
     ( shares 
     
     , trades 
     
     , dollars 
     
     ) 
     
     


     
     

    
     
     return res
     
     


     
     


     
     


     
     def list_book_for_month 
     
     ( year 
     
     , month 
     
     ) :
     
     

    
     
     """List all days for given month, and for each

    day list fact book entry.

    """ 
     
     

    data 
     
     = fetch_month_data 
     
     ( year 
     
     , month 
     
     ) 
     
     


     
     

    cal 
     
     = Calendar 
     
     ( 
     
     ) 
     
     

    
     
     print 
     
     ( 
     
     "%12s | %12s | %12s | %12s" %
     
     

          
     
     ( 
     
     "day" 
     
     , 
     
     "shares" 
     
     , 
     
     "trades" 
     
     , 
     
     "dollars" 
     
     ) 
     
     ) 
     
     

    
     
     print 
     
     ( 
     
     "%12s-+-%12s-+-%12s-+-%12s" %
     
     

          
     
     ( 
     
     "-" * 
     
     12 
     
     , 
     
     "-" * 
     
     12 
     
     , 
     
     "-" * 
     
     12 
     
     , 
     
     "-" * 
     
     12 
     
     ) 
     
     ) 
     
     


     
     

    
     
     for day 
     
     in cal. 
     
     itermonthdates 
     
     ( year 
     
     , month 
     
     ) :
     
     

        
     
     if day. 
     
     month 
     
     != month:
     
     

            
     
     continue 
     
     

        
     
     if day 
     
     in data:
     
     

            shares 
     
     , trades 
     
     , dollars 
     
     = data 
     
     [ day 
     
     ] 
     
     

        
     
     else :
     
     

            shares 
     
     , trades 
     
     , dollars 
     
     = 
     
     0 
     
     , 
     
     0 
     
     , 
     
     0 
     
     


     
     

        
     
     print 
     
     ( 
     
     "%12s | %12s | %12s | %12s" %
     
     

              
     
     ( day 
     
     , shares 
     
     , trades 
     
     , dollars 
     
     ) 
     
     ) 
     
     


     
     


     
     


     
     if __name__ 
     
     == 
     
     '__main__' :
     
     

    year 
     
     = 
     
     int 
     
     ( 
     
     sys . 
     
     argv 
     
     [ 
     
     1 
     
     ] 
     
     ) 
     
     

    month 
     
     = 
     
     int 
     
     ( 
     
     sys . 
     
     argv 
     
     [ 
     
     2 
     
     ] 
     
     ) 
     
     


     
     

    list_book_for_month 
     
     ( year 
     
     , month 
     
     ) 
    
    
   
   

在此实现中，我们使用上面SQL查询来获取结果集，并将其存储在字典中。 该dict的键是一个月中的某天，因此我们可以遍历日历的天数列表，在拥有日历时检索匹配的数据，并在没有任何数据时安装默认结果集（例如零）。

下面是运行程序时的输出。 如您所见，我们选择了类似于psql输出的输出，这使得比较达到相同结果所需的工作变得更加容易。

   
   

    
    

     
     $ ./factbook-month.py 2017 2
     
     

         day |       shares |       trades |      dollars
     
     

-------------+--------------+--------------+-------------
     
     

  2017-02-01 |   1161001502 |      5217859 |  44660060305
     
     

  2017-02-02 |   1128144760 |      4586343 |  43276102903
     
     

  2017-02-03 |   1084735476 |      4396485 |  42801562275
     
     

  2017-02-04 |            0 |            0 |            0
     
     

  2017-02-05 |            0 |            0 |            0
     
     

  2017-02-06 |    954533086 |      3817270 |  37300908120
     
     

  2017-02-07 |   1037660897 |      4220252 |  39754062721
     
     

  2017-02-08 |   1100076176 |      4410966 |  40491648732
     
     

  2017-02-09 |   1081638761 |      4462009 |  40169585511
     
     

  2017-02-10 |   1021379481 |      4028745 |  38347515768
     
     

  2017-02-11 |            0 |            0 |            0
     
     

  2017-02-12 |            0 |            0 |            0
     
     

  2017-02-13 |   1020482007 |      3963509 |  38745317913
     
     

  2017-02-14 |   1041009698 |      4299974 |  40737106101
     
     

  2017-02-15 |   1120119333 |      4424251 |  43802653477
     
     

  2017-02-16 |   1091339672 |      4461548 |  41956691405
     
     

  2017-02-17 |   1160693221 |      4132233 |  48862504551
     
     

  2017-02-18 |            0 |            0 |            0
     
     

  2017-02-19 |            0 |            0 |            0
     
     

  2017-02-20 |            0 |            0 |            0
     
     

  2017-02-21 |   1103777644 |      4323282 |  44416927777
     
     

  2017-02-22 |   1064236648 |      4169982 |  41137731714
     
     

  2017-02-23 |   1192772644 |      4839887 |  44254446593
     
     

  2017-02-24 |   1187320171 |      4656770 |  45229398830
     
     

  2017-02-25 |            0 |            0 |            0
     
     

  2017-02-26 |            0 |            0 |            0
     
     

  2017-02-27 |   1132693382 |      4243911 |  43613734358
     
     

  2017-02-28 |   1455597403 |      4789769 |  57874495227
    
    
   
   

PostgreSQL高级功能

只需使用一个SQL查询即可完成同一件事，而无需花费任何应用程序代码来解决问题：

   
   

    
    

     
       
     
     SELECT 
     
     CAST 
     
     ( calendar 
     
     . entry 
     
     AS 
     
     DATE 
     
     ) 
     
     AS 
     
     DATE 
     
     , 
     
     

         
     
     COALESCE 
     
     ( shares 
     
     , 
     
     0 
     
     ) 
     
     AS shares 
     
     , 
     
     

         
     
     COALESCE 
     
     ( trades 
     
     , 
     
     0 
     
     ) 
     
     AS trades 
     
     , 
     
     

         to_char 
     
     ( 
     
     

             
     
     COALESCE 
     
     ( dollars 
     
     , 
     
     0 
     
     ) 
     
     , 
     
     

             
     
     'L99G999G999G999' 
     
     

         
     
     ) 
     
     AS dollars
     
     

    
     
     FROM 
     
     /*

          * Generate the target month's calendar then LEFT JOIN

          * each day against the factbook dataset, so as to have

          * every day in the result set, whether or not we have a

          * book entry for the day.

          */ 
     
     

         generate_series 
     
     ( 
     
     DATE : 
     
     'start' 
     
     , 
     
     

                         
     
     DATE : 
     
     'start' 
     
     + 
     
     INTERVAL 
     
     '1 month' 
     
     

                                       
     
     - 
     
     INTERVAL 
     
     '1 day' 
     
     , 
     
     

                         
     
     INTERVAL 
     
     '1 day' 
     
     

         
     
     ) 
     
     

         
     
     AS calendar 
     
     ( entry 
     
     ) 
     
     

         
     
     LEFT 
     
     JOIN factbook
     
     

                
     
     ON factbook 
     
     . date 
     
     = calendar 
     
     . entry
     
     


     
     ORDER 
     
     BY 
     
     DATE ;
    
    
   
   

在此查询中，我们使用了几种新的基本SQL和PostgreSQL技术：

• SQL接受以-- comment样式（从行的开头到结尾）运行的-- comment ，或者接受带有/* comment */样式的C样式的/* comment */ 。 与任何编程语言一样，注释最好用于表示意图，否则，仅从代码中进行反向工程可能会很棘手。
• generate_series()是PostgreSQL 集返回函数 ，该文档的内容为：“从头到尾以step为步长生成一系列值。” 由于PostgreSQL知道其日历，因此很容易从任何给定月份生成全天，而该月的第一天作为查询中的单个参数。
• generate_series()是包含性的，很像BETWEEN运算符，因此我们用表达式- interval '1 day'排除了下个月的第一天。
• cast(calendar.entry as date)表达式将生成的calendar.entry转换为date数据类型，该calendar.entry是generate_series()函数调用的结果。 我们需要使用cast因为generate_series()函数返回一组时间戳记条目，在本练习中与我们无关。
• 在left join我们的生成之间calendar表和factbook表将保持每一个calendar行和关联factbook只有当行与它date两个表的列具有相同的值。 当在factbook找不到calendar.date ， factbook列（ year ， date ， shares ， trades和dollars ）将替换为NULL值。
• Coalesce返回其第一个不为null的参数。 因此，表达式coalesce(shares, 0) as shares是我们在factbook表中为此calendar.date行找到的份额，或者当我们没有找到calendar.date条目时为0。 此外， left join保留了我们的结果集行，并在factbook列中填充了NULL值。

最后，这是此查询的结果：

   
   

    
    

     
         date    │   shares   │ trades  │     dollars      
     
     

════════════╪════════════╪═════════╪══════════════════
     
     

 2017-02-01 │ 1161001502 │ 5217859 │ $ 44,660,060,305
     
     

 2017-02-02 │ 1128144760 │ 4586343 │ $ 43,276,102,903
     
     

 2017-02-03 │ 1084735476 │ 4396485 │ $ 42,801,562,275
     
     

 2017-02-04 │          0 │       0 │ $              0
     
     

 2017-02-05 │          0 │       0 │ $              0
     
     

 2017-02-06 │  954533086 │ 3817270 │ $ 37,300,908,120
     
     

 2017-02-07 │ 1037660897 │ 4220252 │ $ 39,754,062,721
     
     

 2017-02-08 │ 1100076176 │ 4410966 │ $ 40,491,648,732
     
     

 2017-02-09 │ 1081638761 │ 4462009 │ $ 40,169,585,511
     
     

 2017-02-10 │ 1021379481 │ 4028745 │ $ 38,347,515,768
     
     

 2017-02-11 │          0 │       0 │ $              0
     
     

 2017-02-12 │          0 │       0 │ $              0
     
     

 2017-02-13 │ 1020482007 │ 3963509 │ $ 38,745,317,913
     
     

 2017-02-14 │ 1041009698 │ 4299974 │ $ 40,737,106,101
     
     

 2017-02-15 │ 1120119333 │ 4424251 │ $ 43,802,653,477
     
     

 2017-02-16 │ 1091339672 │ 4461548 │ $ 41,956,691,405
     
     

 2017-02-17 │ 1160693221 │ 4132233 │ $ 48,862,504,551
     
     

 2017-02-18 │          0 │       0 │ $              0
     
     

 2017-02-19 │          0 │       0 │ $              0
     
     

 2017-02-20 │          0 │       0 │ $              0
     
     

 2017-02-21 │ 1103777644 │ 4323282 │ $ 44,416,927,777
     
     

 2017-02-22 │ 1064236648 │ 4169982 │ $ 41,137,731,714
     
     

 2017-02-23 │ 1192772644 │ 4839887 │ $ 44,254,446,593
     
     

 2017-02-24 │ 1187320171 │ 4656770 │ $ 45,229,398,830
     
     

 2017-02-25 │          0 │       0 │ $              0
     
     

 2017-02-26 │          0 │       0 │ $              0
     
     

 2017-02-27 │ 1132693382 │ 4243911 │ $ 43,613,734,358
     
     

 2017-02-28 │ 1455597403 │ 4789769 │ $ 57,874,495,227
     
     

(28 rows)
    
    
   
   

请注意，我们用一个简单SQL查询替换了60行Python代码。 将来，这意味着需要维护的代码更少，实现的效率也更高。 在这里，Python正在执行Hash Join Nested Loop而PostgreSQL在两个有序关系上选择了“ Merge Left Join 。

计算每周变化

想象一下，分析部门现在希望我们提供每天的每周差异。 这意味着我们需要添加一列，并将其变化计算为每个日期与上周同一天之间的dollars列的百分比。

我使用“每周工作周百分比差异”示例是因为这既是经典的分析需求（尽管可能主要是在营销圈子中），又因为（以我的经验）开发人员的第一React很少是编写SQL查询来做所有的数学。

此外，日历在计算周数方面不是很有帮助，但是对于PostgreSQL，此任务就像拼写单词week一样容易：

   
   

    
    
 
     
     WITH computed_data 
     
     AS 
     
     


     
     ( 
     
     

  
     
     SELECT 
     
     CAST 
     
     ( 
     
     DATE 
     
     AS 
     
     DATE 
     
     )   
     
     AS 
     
     DATE 
     
     , 
     
     

         to_char 
     
     ( 
     
     DATE 
     
     , 
     
     'Dy' 
     
     )  
     
     AS 
     
     DAY 
     
     , 
     
     

         
     
     COALESCE 
     
     ( dollars 
     
     , 
     
     0 
     
     ) 
     
     AS dollars 
     
     , 
     
     

         lag 
     
     ( dollars 
     
     , 
     
     1 
     
     ) 
     
     

           
     
     OVER 
     
     ( 
     
     

             partition 
     
     BY 
     
     EXTRACT 
     
     ( 
     
     'isodow' 
     
     FROM 
     
     DATE 
     
     ) 
     
     

                 
     
     ORDER 
     
     BY 
     
     DATE 
     
     

           
     
     ) 
     
     

         
     
     AS last_week_dollars
     
     

    
     
     FROM 
     
     /*

          * Generate the month calendar, plus a week before

          * so that we have values to compare dollars against

          * even for the first week of the month.

          */ 
     
     

         generate_series 
     
     ( 
     
     DATE : 
     
     'start' 
     
     - 
     
     INTERVAL 
     
     '1 week' 
     
     , 
     
     

                         
     
     DATE : 
     
     'start' 
     
     + 
     
     INTERVAL 
     
     '1 month' 
     
     

                                       
     
     - 
     
     INTERVAL 
     
     '1 day' 
     
     , 
     
     

                         
     
     INTERVAL 
     
     '1 day' 
     
     

         
     
     ) 
     
     

         
     
     AS calendar 
     
     ( 
     
     DATE 
     
     ) 
     
     

         
     
     LEFT 
     
     JOIN factbook 
     
     USING 
     
     ( 
     
     DATE 
     
     ) 
     
     


     
     ) 
     
     

  
     
     SELECT 
     
     DATE 
     
     , 
     
     DAY 
     
     , 
     
     

         to_char 
     
     ( 
     
     

             
     
     COALESCE 
     
     ( dollars 
     
     , 
     
     0 
     
     ) 
     
     , 
     
     

             
     
     'L99G999G999G999' 
     
     

         
     
     ) 
     
     AS dollars 
     
     , 
     
     

         
     
     CASE 
     
     WHEN dollars 
     
     IS 
     
     NOT 
     
     NULL 
     
     

               
     
     AND dollars 
     
     <> 
     
     0 
     
     

              
     
     THEN round 
     
     (  
     
     100.0 
     
     

                         
     
     * 
     
     ( dollars 
     
     - last_week_dollars 
     
     ) 
     
     

                         
     
     / dollars
     
     

                       
     
     , 
     
     2 
     
     ) 
     
     

          
     
     END 
     
     

         
     
     AS 
     
     "WoW %" 
     
     

    
     
     FROM computed_data
     
     

   
     
     WHERE 
     
     DATE 
     
     >= 
     
     DATE : 
     
     'start' 
     
     


     
     ORDER 
     
     BY 
     
     DATE ;
    
    
   
   

要在SQL中实现这种情况，我们需要1992年在SQL标准中出现但通常在SQL类中跳过的窗口函数。 在SQL语句中执行的最后一件事是windows函数，在join操作和where子句之后。 因此，如果要在2月1日之前看到整整一周的时间，则需要将日历选择范围扩展到过去一周的时间，然后再次将我们发布的数据限制给调用方。

这就是为什么我们使用公用表表达式（查询的WITH部分）来获取所需的扩展数据集，包括last_week_dollars计算列。

表达式extract('isodow' from date)是一种标准SQL功能，可以根据ISO规则计算星期几。 用作partition by帧进行partition by子句，它允许一行成为具有相同isodow任何其他行的isodow 。 然后，按日期排序时， lag()窗口函数可以引用先前的对等美元值； 那就是我们要与当前美元价值进行比较的数字。

然后，在查询的主要部分中使用了computed_data结果集作为从中获取数据的关系，并且这次计算更加容易，因为我们last_week_dollars dollars和last_week_dollars列应用了经典的差异百分比公式。

这是运行此查询的结果：

   
   

    
    

     
         date    │ day │     dollars      │ WoW %  
     
     

════════════╪═════╪══════════════════╪════════
     
     

 2017-02-01 │ Wed │ $ 44,660,060,305 │  -2.21
     
     

 2017-02-02 │ Thu │ $ 43,276,102,903 │   1.71
     
     

 2017-02-03 │ Fri │ $ 42,801,562,275 │  10.86
     
     

 2017-02-04 │ Sat │ $              0 │      ¤
     
     

 2017-02-05 │ Sun │ $              0 │      ¤
     
     

 2017-02-06 │ Mon │ $ 37,300,908,120 │  -9.64
     
     

 2017-02-07 │ Tue │ $ 39,754,062,721 │ -37.41
     
     

 2017-02-08 │ Wed │ $ 40,491,648,732 │ -10.29
     
     

 2017-02-09 │ Thu │ $ 40,169,585,511 │  -7.73
     
     

 2017-02-10 │ Fri │ $ 38,347,515,768 │ -11.61
     
     

 2017-02-11 │ Sat │ $              0 │      ¤
     
     

 2017-02-12 │ Sun │ $              0 │      ¤
     
     

 2017-02-13 │ Mon │ $ 38,745,317,913 │   3.73
     
     

 2017-02-14 │ Tue │ $ 40,737,106,101 │   2.41
     
     

 2017-02-15 │ Wed │ $ 43,802,653,477 │   7.56
     
     

 2017-02-16 │ Thu │ $ 41,956,691,405 │   4.26
     
     

 2017-02-17 │ Fri │ $ 48,862,504,551 │  21.52
     
     

 2017-02-18 │ Sat │ $              0 │      ¤
     
     

 2017-02-19 │ Sun │ $              0 │      ¤
     
     

 2017-02-20 │ Mon │ $              0 │      ¤
     
     

 2017-02-21 │ Tue │ $ 44,416,927,777 │   8.28
     
     

 2017-02-22 │ Wed │ $ 41,137,731,714 │  -6.48
     
     

 2017-02-23 │ Thu │ $ 44,254,446,593 │   5.19
     
     

 2017-02-24 │ Fri │ $ 45,229,398,830 │  -8.03
     
     

 2017-02-25 │ Sat │ $              0 │      ¤
     
     

 2017-02-26 │ Sun │ $              0 │      ¤
     
     

 2017-02-27 │ Mon │ $ 43,613,734,358 │      ¤
     
     

 2017-02-28 │ Tue │ $ 57,874,495,227 │  23.25
     
     

(28 rows)
    
    
   
   

玩得开心，编写代码，而SQL是代码，玩得开心！

本文基于Dimitri Fontaine的《 在应用程序开发中掌握PostgreSQL的掌握 》一书的摘录，该书解释了如何用简单的查询替换成千上万行代码。 本书对这些主题进行了更详细的介绍，并提供了许多其他示例，因此您可以精通PostgreSQL并发出SQL查询以准确获取所需的结果集。

翻译自: https://opensource.com/article/17/12/python-and-postgresql