Resource query example (postgresql)

plan.md

SELECT
  components.*,
  COALESCE(SUM(previous_month.resource_counts), 0.0) AS previous_month_resource_counts,
  COALESCE(SUM(previous_month.unblended_cost), 0.0) AS previous_month_unblended_cost,
  COALESCE(SUM(previous_month.blended_cost), 0.0) AS previous_month_blended_cost,
  COALESCE(SUM(month_to_date.resource_counts), 0.0) AS month_to_date_resource_counts,
  COALESCE(SUM(month_to_date.unblended_cost), 0.0) AS month_to_date_unblended_cost,
  COALESCE(SUM(month_to_date.blended_cost), 0.0) AS month_to_date_blended_cost
FROM "components"
INNER JOIN "resources"
  ON "resources"."component_id" = "components"."id"
LEFT OUTER JOIN (SELECT
  "unified_billing_daily_line_items"."resource_id",
  COALESCE(SUM(unified_billing_daily_line_items.unblended_cost), 0.0) AS unblended_cost,
  COALESCE(SUM(unified_billing_daily_line_items.blended_cost), 0.0) AS blended_cost,
  COUNT(DISTINCT unified_billing_daily_line_items.resource_uuid) AS resource_counts
FROM "unified_billing_daily_line_items"
WHERE (usage_date BETWEEN '2015-09-01 00:00:00.000000' AND '2015-09-30 23:59:59.999999')
GROUP BY resource_id) AS previous_month
  ON previous_month.resource_id = resources.id
LEFT OUTER JOIN (SELECT
  "unified_billing_daily_line_items"."resource_id",
  COALESCE(SUM(unified_billing_daily_line_items.unblended_cost), 0.0) AS unblended_cost,
  COALESCE(SUM(unified_billing_daily_line_items.blended_cost), 0.0) AS blended_cost,
  COUNT(DISTINCT unified_billing_daily_line_items.resource_uuid) AS resource_counts
FROM "unified_billing_daily_line_items"
WHERE (usage_date BETWEEN '2015-10-01 00:00:00.000000' AND '2015-10-31 23:59:59.999999')
GROUP BY resource_id) AS month_to_date
  ON month_to_date.resource_id = resources.id
GROUP BY components.id

                                                                             QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=46015.30..46025.47 rows=407 width=299)
   Group Key: components.id
   ->  Merge Left Join  (cost=43228.25..45945.54 rows=3986 width=299)
         Merge Cond: (resources.id = unified_billing_daily_line_items_1.resource_id)
         ->  Merge Left Join  (cost=28144.21..30020.27 rows=3986 width=231)
               Merge Cond: (resources.id = unified_billing_daily_line_items.resource_id)
               ->  Sort  (cost=444.20..454.17 rows=3986 width=159)
                     Sort Key: resources.id
                     ->  Hash Join  (cost=17.16..205.83 rows=3986 width=159)
                           Hash Cond: (resources.component_id = components.id)
                           ->  Seq Scan on resources  (cost=0.00..133.86 rows=3986 width=8)
                           ->  Hash  (cost=12.07..12.07 rows=407 width=155)
                                 ->  Seq Scan on components  (cost=0.00..12.07 rows=407 width=155)
               ->  Materialize  (cost=27700.01..29538.85 rows=1383 width=76)
                     ->  GroupAggregate  (cost=27700.01..29521.57 rows=1383 width=50)
                           Group Key: unified_billing_daily_line_items.resource_id
                           ->  Sort  (cost=27700.01..28060.17 rows=144065 width=50)
                                 Sort Key: unified_billing_daily_line_items.resource_id
                                 ->  Seq Scan on unified_billing_daily_line_items  (cost=0.00..10431.76 rows=144065 width=50)
                                       Filter: ((usage_date >= '2015-09-01'::date) AND (usage_date <= '2015-09-30'::date))
         ->  Materialize  (cost=15084.04..15907.56 rows=620 width=76)
               ->  GroupAggregate  (cost=15084.04..15899.81 rows=620 width=50)
                     Group Key: unified_billing_daily_line_items_1.resource_id
                     ->  Sort  (cost=15084.04..15245.34 rows=64517 width=50)
                           Sort Key: unified_billing_daily_line_items_1.resource_id
                           ->  Bitmap Heap Scan on unified_billing_daily_line_items unified_billing_daily_line_items_1  (cost=1373.72..7721.48 rows=64517 width=50)
                                 Recheck Cond: ((usage_date >= '2015-10-01'::date) AND (usage_date <= '2015-10-31'::date))
                                 ->  Bitmap Index Scan on unified_billing_daily_usage_date  (cost=0.00..1357.59 rows=64517 width=0)
                                       Index Cond: ((usage_date >= '2015-10-01'::date) AND (usage_date <= '2015-10-31'::date))

resources.sql

create table owners (
  id serial primary key,
  name varchar not null
);

create table resources (
  id serial primary key,
  owner_id integer not null references owners(id),
  name varchar not null,
  restype varchar not null
);

create table line_items (
  id serial primary key,
  resource_id integer not null references resources(id),
  usage_date timestamp without time zone not null,
  cost money not null default 0.0
);

select
  owners.id as owner_id,
  owners.name as owner_name,
  resources.id as resource_id,
  resources.name as resource_name,
  coalesce(wtd.total_cost, 0.0::money) as wtd_cost,
  coalesce(mtd.total_cost, 0.0::money) as mtd_cost
from owners
left outer join resources on resources.owner_id = owners.id
left outer join (
  select
    resource_id,
    sum(cost) as total_cost
  from line_items
  where usage_date >= date_trunc('week', current_date) 
    and usage_date < date_trunc('week', current_date) + '1 week'::interval
  group by resource_id
) as wtd on wtd.resource_id = resources.id
left outer join (
  select
    resource_id,
    sum(cost) as total_cost
  from line_items
  where usage_date >= date_trunc('month', current_date) 
    and usage_date < date_trunc('month', current_date) + '1 month'::interval
  group by resource_id
) as mtd on mtd.resource_id = resources.id;

select
  owners.id as owner_id,
  owners.name as owner_name,
  resources.id as resource_id,
  resources.name as resource_name,
  coalesce(sum(case
    when usage_date >= date_trunc('week', current_date) and 
          usage_date < date_trunc('week', current_date) + '1 week'::interval 
      then cost
    else 0.0::money
  end), 0.0::money) as wtd_cost,
  coalesce(sum(case
    when usage_date >= date_trunc('month', current_date) and 
          usage_date < date_trunc('month', current_date) + '1 month'::interval 
      then cost
    else 0.0::money
  end), 0.0::money) as mtd_cost
from owners
left outer join resources on resources.owner_id = owners.id
left outer join line_items on line_items.resource_id = resources.id
group by owners.id, resources.id;