sql - 在 SQL 中使用日期列分组

Question

我有大量历史数据，我想按月分组。我正在准备以下对 HP Vertica 数据库的查询，以获取按月分组的数据。

SELECT 
      Region, Country,
      TO_TIMESTAMP(TO_CHAR(Order_Date,'m/yyyy'),'m/yyyy') as MONTH_Order_Date, 
      SUM(CAST(Unit_Price as NUMERIC(37,15))) as Sum_Unit_Price, 
      SUM(CAST(Total_Revenue as NUMERIC(37,15))) as Sum_Total_Revenue 
FROM SalesRecords 
GROUP BY Region,Country,TO_TIMESTAMP(TO_CHAR(Order_Date,'m/yyyy'),'m/yyyy')
ORDER BY Region ASC,Country ASC,TO_TIMESTAMP(TO_CHAR(Order_Date,'m/yyyy'),'m/yyyy') ASC

现在这个查询的问题是性能，超过 10 亿条数据需要将近 2 分钟，并且在 group by 之后返回的记录集是 10 万行。我需要下面显示的格式的数据，因此我需要在数据库级别解析日期以获得格式化日期，并且这种格式化在数据库服务器上需要时间。

请建议我可以使用的任何其他方式，因为在同一张表上，如果我在除日期类型列之外的其他列上对数据进行分组，性能很好，返回的记录集几乎在 40 秒内超过 10 亿条记录。

我需要为所有主要的 RDBMS 执行此操作。

Region  Country     MONTH_Order_Date    Sum_Unit_Price          Sum_Total_Revenue
Asia    Bangladesh  2010-01-01 00:00:00 186252.349999999999937  920607119.140000001169023
Asia    Bangladesh  2011-01-01 00:00:00 186456.190000000000641  931633189.440000000452752
Asia    Bangladesh  2012-01-01 00:00:00 194925.550000000000312  969718040.969999998663069
Asia    Bangladesh  2013-01-01 00:00:00 194048.289999999999566  1017253078.219999998891571
Asia    Bangladesh  2014-01-01 00:00:00 184143.090000000000413  915867255.449999996567606
Asia    Bangladesh  2015-01-01 00:00:00 193697.769999999999864  959097995.869999999053554
Asia    Bangladesh  2016-01-01 00:00:00 184833.730000000000529  955360230.500000001682568
Asia    Bangladesh  2017-01-01 00:00:00 111476.840000000000014  563824376.189999998257226
Asia    Bhutan      2010-01-01 00:00:00 186506.900000000000474  916963415.479999997623498 

Answer 1

Vertica 游程编码 (RLE) 允许您利用低基数列来提高查询性能和节省磁盘空间，将时间戳等非常精细的数据类型转换为 mmyyyy 可能非常适合这项工作。

1) 将 mmyyyy 转换放入您的数据加载过程

2) 使用名为 month_year 且格式为 mmyyyy 的新计算列创建一个新投影。

3) 在投影列列表和投影 ORDER BY 中，将此列和其他低卡列放在列表的开头。大牌列在列表的末尾。但是任何高卡连接列(如键)都应该紧跟在低卡列之后。

4) 请务必在此列和所有其他低卡列上指定 ENCODING_RLE。

这两个指向 Vertica 文档的链接解释了所涉及的概念:

Choosing Sort Order: Best Practices

Optimizing Queries for Projections with Predicates