本文共 21609 字，大约阅读时间需要 72 分钟。
Python是一种编程语言，可让您快速工作并更有效地集成系统，而PostgreSQL是世界上最先进的开源数据库。 这两个人在一起工作得很好。 本文介绍解决简单问题时如何充分利用PostgreSQL（psql）。 尽管将Python代码引发问题很诱人，但它并不总是最佳选择。
SQL具有相当大的处理能力，将SQL集成到您的工作流中通常意味着编写更少的代码行。 正如所说的那样，代码行就是花费的行：
放心的幻想充斥着这种做法，即程序就像其他任何设备一样，唯一的区别是程序的制造可能需要一种新型的工匠，即。 程序员。 从那里开始，按照“每月产生的代码行数”衡量“程序员生产率”仅一步之遥。 这是一个非常昂贵的度量单位，因为它鼓励编写平淡的代码，但是今天，即使从纯粹的业务角度来看，我对它的愚蠢程度也不再感兴趣。 我今天的观点是，如果我们希望对代码行进行计数，我们不应将其视为“生产的行”，而应视为“花费的行”：当前的传统观点是如此愚蠢，以至于将计数视为错误的一面。分类帐。 — 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连接发送其内容。 该文件中有很多数据，因此在此示例中使用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
只需使用一个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技术：
-- 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(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并发出SQL查询以准确获取所需的结果集。
翻译自:
转载地址：http://crbzd.baihongyu.com/