mysql postgresql postgresql-9.4 integral

Postgresql 9.4: Use calculated column in the same query and group partial results by month

I am querying a postgresql 9.4 database and I want to perform calculations using columns inside the same query.

The result I am trying to obtain is a partial value based on the number of days passed out of a total_days amount. E.g.

start_date: 01/01/2016,
duration_in_months: 2,
total_days: 60,
value_x: 120.

If I launch the query today, 05/01/2016, I want to obtain:

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

In my dataset, I have over 100k records and I need to get this partial value grouped by month (adding the partials month by month).

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

In MySQL I am able to do calculation as follows:

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;

Following the instructions found in this question previously asked, I am currently using in PostgreSQL a query like:

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;

I would need your help in order to:

use calculated variables in PostgreSQL like in MySQL using formulas in the query or find another way to make the query more readable
group the partials results by months
insert a where clause to ensure that

passed_days >= 0 and passed_days <= total_days

Thank you very much in advance and feel free to ask for more details.

Solution

I found a proper solution to do it in PostgreSQL:

grouping by month: use with table as ( ) statement at the beginning of he query. Then do an inner join
declare variables: use subqueries

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

WITH time_ranges AS (
SELECT       to_date('2014-07-01', 'yyyy-mm-dd') as START_DATE, to_date('2014-07-31', 'yyyy-mm-dd') as END_DATE
  UNION SELECT to_date('2014-07-01', 'yyyy-mm-dd'), to_date('2014-08-31', 'yyyy-mm-dd')
  UNION SELECT to_date('2014-07-01', 'yyyy-mm-dd'), to_date('2014-09-30', 'yyyy-mm-dd')
  UNION SELECT to_date('2014-07-01', 'yyyy-mm-dd'), to_date('2014-10-31', 'yyyy-mm-dd')
  UNION SELECT to_date('2014-07-01', 'yyyy-mm-dd'), to_date('2014-11-30', 'yyyy-mm-dd')
  UNION SELECT to_date('2014-07-01', 'yyyy-mm-dd'), to_date('2014-12-31', 'yyyy-mm-dd')
  UNION SELECT to_date('2014-07-01', 'yyyy-mm-dd'), to_date('2015-01-31', 'yyyy-mm-dd')
  UNION SELECT to_date('2014-07-01', 'yyyy-mm-dd'), to_date('2015-02-28', 'yyyy-mm-dd')
  UNION SELECT to_date('2014-07-01', 'yyyy-mm-dd'), to_date('2015-03-31', 'yyyy-mm-dd')
  UNION SELECT to_date('2014-07-01', 'yyyy-mm-dd'), to_date('2015-04-30', 'yyyy-mm-dd')
  UNION SELECT to_date('2014-07-01', 'yyyy-mm-dd'), to_date('2015-05-31', 'yyyy-mm-dd')
  UNION SELECT to_date('2014-07-01', 'yyyy-mm-dd'), to_date('2015-06-30', 'yyyy-mm-dd')
  UNION SELECT to_date('2014-07-01', 'yyyy-mm-dd'), to_date('2015-07-31', 'yyyy-mm-dd')
  UNION SELECT to_date('2014-07-01', 'yyyy-mm-dd'), to_date('2015-08-31', 'yyyy-mm-dd')
  UNION SELECT to_date('2014-07-01', 'yyyy-mm-dd'), to_date('2015-09-30', 'yyyy-mm-dd')
  UNION SELECT to_date('2014-07-01', 'yyyy-mm-dd'), to_date('2015-10-31', 'yyyy-mm-dd')
  UNION SELECT to_date('2014-07-01', 'yyyy-mm-dd'), to_date('2015-11-30', 'yyyy-mm-dd')
  UNION SELECT to_date('2014-07-01', 'yyyy-mm-dd'), to_date('2015-12-31', 'yyyy-mm-dd')
  UNION SELECT to_date('2014-07-01', 'yyyy-mm-dd'), to_date('2016-01-05', 'yyyy-mm-dd')
)

SELECT time_ranges.end_date, round(SUM(gross_pdu * LEAST(total_days, GREATEST( EXTRACT(DAY FROM(time_ranges.end_date - guarantees_days.start_date)), 0) ) / total_days)::numeric, 2)
FROM
(SELECT
  *,
  EXTRACT(DAY FROM (start_date + (duration_in_months || ' month')::INTERVAL) - start_date) as total_days
FROM subscribed_guarantees
) as guarantees_days
INNER JOIN
time_ranges ON
time_ranges.start_date <= guarantees_days.start_date AND guarantees_days.start_date <= time_ranges.end_date
WHERE INSURANCE_COMPANY = 'INSURANCE COMPANY' AND TAX = 13.5
 GROUP BY
  time_ranges.end_date
 ORDER BY
  time_ranges.end_date