PostgreSQL: windowing to replace multiple sub-queries ... hopefully

Question

I have two tables:

CREATE TABLE fund_data
(
  id serial NOT NULL,
  fund_entries_id integer NOT NULL,
  fund_val numeric(25,6) NOT NULL,
  bbg_pulls_id integer NOT NULL,
  CONSTRAINT fund_data_pkey PRIMARY KEY (id),
  CONSTRAINT fund_data_bbg_pulls_id_fkey FOREIGN KEY (bbg_pulls_id)
      REFERENCES bbg_pulls (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fund_data_fund_entries_id_fkey FOREIGN KEY (fund_entries_id)
      REFERENCES fund_entries (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fund_data_fund_entries_id_bbg_pulls_id_key UNIQUE (fund_entries_id, bbg_pulls_id)
)

And:

CREATE TABLE fund_entries
(
  id serial NOT NULL,
  fiscal_prd_end_date date NOT NULL,
  company_id integer NOT NULL,
  ern_dt_id integer NOT NULL,
  CONSTRAINT fund_entries_pkey PRIMARY KEY (id),
  CONSTRAINT fund_entries_company_id_fkey FOREIGN KEY (company_id)
      REFERENCES company (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fund_entries_ern_dt_id_fkey11 FOREIGN KEY (ern_dt_id)
      REFERENCES ern_dt (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fund_entries_company_id_fiscal_prd_end_date_key UNIQUE (company_id, fiscal_prd_end_date),
  CONSTRAINT fund_entries_ern_dt_id_key UNIQUE (ern_dt_id)
)

To get the most recent value from fund_data for a given company_id and bbg_pulls_id, I currently do two correlated sub-queries:

SELECT fd.fund_val
FROM fund_data fd
    ,(
        SELECT fe.id
        FROM fund_entries fe
            ,(
                SELECT max(fe.fiscal_prd_end_date)
                FROM fund_entries fe
                WHERE fe.company_id = 858
                ) md
        WHERE fe.fiscal_prd_end_date = md.max
            AND fe.company_id = 858
        ) mi
WHERE fd.bbg_pulls_id = 20
    AND fd.fund_entries_id = mi.id

Question: The query works find, but can I use windowing to avoid the two sub-queries? I've tried several times and I keep getting multiple rows.

EDIT: A description of the query in words: Find the max date for a given company_id, so I can find the id for that record. Then use that id to get the most recent value associated with that fk id (and max date) for a given bbg_pulls_id and company. Hopefully that helps.

Answer 1

If I understand correctly:

SELECT DISTINCT
     last_value(fd.fund_val) OVER (ORDER BY fe.fiscal_prd_end_date)
FROM
     fund_data AS fd
     JOIN fund_entries AS fe ON (fd.fund_entries_id = fe.id)
WHERE
     fd.bbg_pulls_id = 20
     AND fe.company_id = 858

UPDATED:

Or just:

SELECT
     fd.fund_val
FROM
     fund_data AS fd
     JOIN fund_entries AS fe ON (fd.fund_entries_id = fe.id)
WHERE
     fd.bbg_pulls_id = 20
     AND fe.company_id = 858
ORDER BY fe.fiscal_prd_end_date DESC
LIMIT 1

Answer 2

SELECT fd.fund_val
FROM fund_data fd
    ,(
        SELECT fe.id
        FROM fund_entries fe
            ,(
                SELECT max(fe.fiscal_prd_end_date)
                FROM fund_entries fe
                WHERE fe.company_id = 858
                ) md
        WHERE fe.fiscal_prd_end_date = md.max
            AND fe.company_id = 858
        ) mi
WHERE fd.bbg_pulls_id = 20
    AND fd.fund_entries_id = mi.id

Seems to be the same thing as

SELECT fd.fund_val
FROM fund_data fd
    ,(
        SELECT fe.id
        FROM fund_entries fe
        WHERE fe.fiscal_prd_end_date = (
                SELECT max(fe.fiscal_prd_end_date)
                FROM fund_entries fe
                WHERE fe.company_id = 858
        )
        AND fe.company_id = 858
     ) mi
WHERE fd.bbg_pulls_id = 20
    AND fd.fund_entries_id = mi.id

We don't ever use implicit joins, so let's just rewrite that too..

SELECT fd.fund_val
FROM fund_data fd
INNER JOIN (
        SELECT fe.id
        FROM fund_entries fe
        WHERE fe.fiscal_prd_end_date = (
                SELECT max(fe.fiscal_prd_end_date)
                FROM fund_entries fe
                WHERE fe.company_id = 858
        )
        AND fe.company_id = 858
) mi
  ON (fd.fund_entries_id = mi.id)
WHERE fd.bbg_pulls_id = 20;

We can clean that up a little more like this..

SELECT fd.fund_val
FROM fund_data fd
INNER JOIN fund_entries AS fe
  ON (fd.fund_entries_id = fe.id)
WHERE fe.fiscal_prd_end_date = (
    SELECT max(fe.fiscal_prd_end_date)
    FROM fund_entries fe
    WHERE fe.company_id = 858
)
AND fe.company_id = 858
AND fd.bbg_pulls_id = 20;

We can rewrite that where-clause too,

SELECT fd.fund_val
FROM fund_data fd
INNER JOIN fund_entries AS fe1
  ON (fd.fund_entries_id = fe1.id)
INNER JOIN (
    SELECT fe.company_id, max(fe.fiscal_prd_end_date)
    FROM fund_entries fe
    GROUP BY company_id
) AS fe2
  ON (
    fe1.company_id = fe2.company_id
    AND fe1.fiscal_prd_end_date = fe2.max
  )
WHERE fe1.company_id = 858
AND fd.bbg_pulls_id = 20;

Update 1

In light of the fact that you can not have ties, this should be even faster using DISTINCT ON

SELECT DISTINCT ON ( company_id, fiscal_prd_end_date ) fd.fund_val
FROM fund_data fd
INNER JOIN fund_entries AS fe1
  ON (fd.fund_entries_id = fe1.id)
WHERE fe1.company_id = 858
AND fd.bbg_pulls_id = 20
ORDER BY company_id, fiscal_prd_end_date DESC;

Also, because you're only using one company, fe1.company_id = 858, you should be able to use a LIMIT here.. Which makes this exactly the same as what the other answer provided @Roman Tkachuk who wins it -- that method is even faster. You just were doing a lot of work before with your original query. It takes a lot of time to clean it up and figure out what you're needing. But, I agree with Roman, if you want just one company_id, the easy way is with a limit and one simple join.