在sql中，计算组查询中的日期部分与日期查找表(in sql, calculating date parts versus date lookup table in group queries)

当基表日期是date或timestamp时，许多查询按周，月或季度进行。

通常，在group by查询group by ，是否使用 - 日期上的函数 - 具有预先计算的提取的日表是否重要

注意：与DATE查找表类似的问题（1990/01/01：2041/12/31）

例如，在postgresql

create table sale( tran_id serial primary key, tran_dt date not null default current_date, sale_amt decimal(8,2) not null, ... ); create table days( day date primary key, week date not null, month date not null, quarter date non null ); -- week query 1: group using funcs select date_trunc('week',tran_dt)::date - 1 as week, count(1) as sale_ct, sum(sale_amt) as sale_amt from sale where date_trunc('week',tran_dt)::date - 1 between '2012-1-1' and '2011-12-31' group by date_trunc('week',tran_dt)::date - 1 order by 1; -- query 2: group using days select days.week, count(1) as sale_ct, sum(sale_amt) as sale_amt from sale join days on( days.day = sale.tran_dt ) where week between '2011-1-1'::date and '2011-12-31'::date group by week order by week;

对我而言， date_trunc()函数似乎更有机， days表更容易使用。

这里有什么比品味更重要吗？

many queries are by week, month or quarter when the base table date is either date or timestamp.

in general, in group by queries, does it matter whether using - functions on the date - a day table that has extraction pre-calculated

note: similar question as DATE lookup table (1990/01/01:2041/12/31)

for example, in postgresql

create table sale( tran_id serial primary key, tran_dt date not null default current_date, sale_amt decimal(8,2) not null, ... ); create table days( day date primary key, week date not null, month date not null, quarter date non null ); -- week query 1: group using funcs select date_trunc('week',tran_dt)::date - 1 as week, count(1) as sale_ct, sum(sale_amt) as sale_amt from sale where date_trunc('week',tran_dt)::date - 1 between '2012-1-1' and '2011-12-31' group by date_trunc('week',tran_dt)::date - 1 order by 1; -- query 2: group using days select days.week, count(1) as sale_ct, sum(sale_amt) as sale_amt from sale join days on( days.day = sale.tran_dt ) where week between '2011-1-1'::date and '2011-12-31'::date group by week order by week;

to me, whereas the date_trunc() function seems more organic, the the days table is easier to use.

is there anything here more than a matter of taste?

最满意答案

-- query 3: group using instant "immediate" calendar table WITH calender AS ( SELECT ser::date AS dd , date_trunc('week', ser)::date AS wk -- , date_trunc('month', ser)::date AS mon -- , date_trunc('quarter', ser)::date AS qq FROM generate_series( '2012-1-1' , '2012-12-31', '1 day'::interval) ser ) SELECT cal.wk , count(1) as sale_ct , sum(sa.sale_amt) as sale_amt FROM sale sa JOIN calender cal ON cal.dd = sa.tran_dt -- WHERE week between '2012-1-1' and '2011-12-31' GROUP BY cal.wk ORDER BY cal.wk ;

注意：我在BETWEEN范围内修正了一个明显的拼写错误。

更新：我使用Erwin的递归CTE来挤出重复的date_trunc（）。嵌套CTE嘉豪：

WITH calendar AS ( WITH RECURSIVE montag AS ( SELECT '2011-01-01'::date AS dd UNION ALL SELECT dd + 1 AS dd FROM montag WHERE dd < '2012-1-1'::date ) SELECT mo.dd, date_trunc('week', mo.dd + 1)::date AS wk FROM montag mo ) SELECT cal.wk , count(1) as sale_ct , sum(sa.sale_amt) as sale_amt FROM sale sa JOIN calendar cal ON cal.dd = sa.tran_dt -- WHERE week between '2012-1-1' and '2011-12-31' GROUP BY cal.wk ORDER BY cal.wk ; -- query 3: group using instant "immediate" calendar table WITH calender AS ( SELECT ser::date AS dd , date_trunc('week', ser)::date AS wk -- , date_trunc('month', ser)::date AS mon -- , date_trunc('quarter', ser)::date AS qq FROM generate_series( '2012-1-1' , '2012-12-31', '1 day'::interval) ser ) SELECT cal.wk , count(1) as sale_ct , sum(sa.sale_amt) as sale_amt FROM sale sa JOIN calender cal ON cal.dd = sa.tran_dt -- WHERE week between '2012-1-1' and '2011-12-31' GROUP BY cal.wk ORDER BY cal.wk ;

Note: I fixed an apparent typo in the BETWEEN range.

UPDATE: I used Erwin's recursive CTE to squeeze out the duplicated date_trunc(). Nested CTE galore:

WITH calendar AS ( WITH RECURSIVE montag AS ( SELECT '2011-01-01'::date AS dd UNION ALL SELECT dd + 1 AS dd FROM montag WHERE dd < '2012-1-1'::date ) SELECT mo.dd, date_trunc('week', mo.dd + 1)::date AS wk FROM montag mo ) SELECT cal.wk , count(1) as sale_ct , sum(sa.sale_amt) as sale_amt FROM sale sa JOIN calendar cal ON cal.dd = sa.tran_dt -- WHERE week between '2012-1-1' and '2011-12-31' GROUP BY cal.wk ORDER BY cal.wk ;

更多推荐