sql - 根据日期选择费率

Question

我正在尝试根据正在搜索的星期几从表中选择 2 个汇率列中的 1 个。我正在尝试为此查询使用两个表:一个日历表(表名=日历，列名=caldates)，它严格来说是一个日期列表，其中每一行都是 2012-06-30 和 2014-05-31 之间的日期.我还有另一个包含 4 列的表(表名=rates):start_date、end_date、weekday_rate 和 weekend_rate。

Start_date      end_date     weekday_rate     weekend_rate
"2012-05-01"  "2012-06-30"        69              150
"2012-07-01"  "2012-08-31"        74              200
"2012-09-01"  "2012-11-14"        75              210
"2012-11-15"  "2013-01-31"        90              150

执行查询时，系统需要查找正确的汇率。现在我的查询看起来像这样:

SELECT  
    CASE cast(extract(dow from caldates) as int)
    WHEN '0'  then (select weekday_rate from rates)
    WHEN '1'  then (select weekday_rate from rates)
    WHEN '2'  then (select weekday_rate from rates)
    WHEN '3'  then (select weekday_rate from rates)
    WHEN '4'  then (select weekday_rate from rates)
    WHEN '5'  then (select weekend_rate from rates)
    When '6'  then (select weekend_rate from rates)
    End AS the_date_rate
FROM calendar WHERE caldates >= '2012-08-30' and  caldates <=  '2012-09-04' ;

当我运行查询时，我收到错误“错误:用作表达式的子查询返回多于一行。”

我希望输出显示日期范围以及与日期关联的汇率。上面的例子看起来像

caldates        the_date_rate
2012-08-30           74
2012-08-31           200
2012-09-01           210
2012-09-02           75
2012-09-03           75
2012-09-04           75

有什么想法吗？

Answer 1

这可以大大简化。

SELECT d.day
      ,CASE WHEN extract(isodow from d.day) < 6
         THEN weekday_rate
         ELSE weekend_rate
       END AS rate
FROM  (SELECT generate_series('2012-06-30'::date
                             ,'2014-05-31'::date, '1d'::interval)::date AS day
      ) d
LEFT   JOIN rates r ON d.day BETWEEN r.start_date AND r.end_date
ORDER  BY d.day

要点

• 您不需要 calendar 表来表示 PostgreSQL 中的连续日期系列。使用 generate_series()就像演示的那样。

• 您需要加入 rates 表。当前接受的答案根本行不通。

• 我使用 LEFT JOIN，因此没有匹配汇率的一天仍然会在结果中以 NULL 作为汇率。

• 当前接受的答案将周日视为工作日，将周五视为周末。我不认为这是预期的。
  CASE 语句也可以简单得多，只需使用 extract(isodow FROM source) 的两种情况。 .

• 您不必将 extract() 的结果转换为 integer。我们比较的文字会自动转换为匹配类型 double 。