mysql - PostgreSQL 9.4 : Use calculated column in the same query and group partial results by month

Question

我正在查询 postgresql 9.4 数据库，并且我想使用同一查询中的列执行计算。

我试图获取的结果是基于total_days金额中过去的天数的部分值。例如

• 开始日期:2016 年 1 月 1 日，
• 持续时间:2，
• 总天数:60，
• value_x:120。

如果我今天(2016 年 5 月 1 日)启动查询，我想获得:

partial_result = value_x * passed_days / total_days
                  120   *      5      /    60

在我的数据集中，我有超过 100k 条记录，我需要获取按月分组的部分值(逐月添加部分值)。

================================================== =========================

在MySQL中，我能够进行如下计算:

SELECT 
  start_date,
  duration_in_months, 
  @end_date:= DATE_ADD(start_date, INTERVAL duration_in_months MONTH) as end_date,
  @total_days:= DATEDIFF(@end_date, start_date),
  @passed_days:= DATEDIFF(CURDATE(), start_date),
  value_x,
  (value_x * @passed_days / @total_days) as partial_result

  FROM table;

按照此 question previously asked 中的说明进行操作，我目前在 PostgreSQL 中使用如下查询:

SELECT
  start_date,
  duration_in_months,
  end_date,
  total_days,
  value_x,
  (value_x * passed_days / total_days) as partial_result

  FROM (SELECT *,
         (start_date + (duration_in_months || ' month')::INTERVAL) as end_date,
         EXTRACT(DAY FROM (start_date + (duration_in_months || ' month')::INTERVAL) - start_date) as total_days, 
         EXTRACT(DAY FROM current_date - start_date) as passed_days
        FROM table) as table1;

我需要您的帮助才能:

• 在 PostgreSQL 中使用计算变量，就像在 MySQL 中一样，在查询中使用公式，或者找到其他方法使查询更具可读性
• 按月份对部分结果进行分组

• 插入一个where子句以确保

  passed_days >= 0 并且passed_days <=total_days

提前非常感谢您，请随时询问更多详细信息。

Answer 1

首先，不保证您的 MySQL 查询能够正常工作。 MySQL 文档非常明确地指出，SELECT 中表达式的求值顺序可以是任意的。因此，可以在设置变量之前计算最后一个表达式(实际上它们将被设置为前一行中的值)。

在 Postgres 中，我认为您对子查询或 CTE 有正确的想法。您只需引用不带 @ 的列。我不知 Prop 体的日期算术是否正确，但这是等效的查询:

SELECT start_date, duration_in_months, end_date, total_days, value_x,
       (value_x * passed_days / total_days) as partial_result
FROM (SELECT t.*,
             (start_date + (duration_in_months || ' month')::INTERVAL) as end_date,
             EXTRACT(DAY FROM (start_date + (duration_in_months || ' month')::INTERVAL) - start_date) as total_days, 
             EXTRACT(DAY FROM current_date - start_date) as passed_days
      FROM table t
     ) t;

extract(day) 在我看来是错误的，但您是从 interval 而不是日期/时间表达式中提取日期。我认为它可以满足您的要求。