Обсуждение: Combining data from Temp Tables

On 2/21/2012 11:31 AM, Jeff Herman wrote:
> Obviously my formatting did not post correctly the first time. Here is
> another attempt, with the desired result table listed:
>
> Temp Table 1
>
> DATE LN MBRID DS
>
> 1/1 A 1 30
>
> 1/1 A 1 30
>
> 1/1 B 1 30
>
> 1/1 A 2 30
>
> 1/1 A 2 30
>
> 1/1 C 3 45
>
> 1/1 D 4 45
>
> 1/1 D 4 45
>
> Temp Table 2
>
> DATE LN MBRID DS
>
> 1/1* A 1 -30
>
> 1/1* A 2 -30
>
> 1/1* A 2 -30
>
> 1/6* D 4 -45
>
> *including and up to five days after Temp Table 1 DATE
>
> Temp Table 3 (desired)
>
> DATE LN MBRID DS
>
> 1/1 A 1 30
>
> 1/1 B 1 30
>
> 1/1 C 3 45
>
> 1/1 D 4 45
>
> Thanks again.
>
> Jeff Herman
>
> HVPA, Database Programmer
>
> Phone: 734.973.0137 ext 441
>
> Fax: 734.975.1248
>
> hermanj@hvpa.com
>
> *From:*pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] *On Behalf Of *Jeff Herman
> *Sent:* Tuesday, February 21, 2012 12:04 PM
> *To:* pgsql-general@postgresql.org
> *Subject:* [GENERAL] Combining data from Temp Tables
>
> Hi all,
>
> I have created two temp tables that I would like to combine to make a
> third temp table and am stuck on how to combine them to get the results
> I want. Any guidance you could give would be appreciated.
>
> Temp Table 1 Temp Table 2
>
> _DATE_ _LN_ _MBRID_ _DS_ _DATE_ _LN_ _MBRID_ _DS_
>
> 1/1 A 1 30 1/1* A 1 -30
>
> *1/1 A 1 30*
>
> *1/1 B 1 30*
>
> 1/1 A 2 30 1/1* A 2 -30
>
> 1/1 A 2 30 1/1* A 2 -30
>
> *1/1 C 3 45*
>
> 1/1 D 4 45 1/6* D 4 -45
>
> *1/1 D 4 45*
>
> *including and up to five days after Temp Table 1 DATE
>
> I would like the Temp Table 3 to hold only the *BOLD* records above. In
> essence, it would compare and erase a record at a one to one ratio based
> on some fields being identical and the date range being on or within the
> next five days of Temp Table 1 date.
>
> Thanks!
>
> Jeff Herman
>
> HVPA, Database Programmer
>
> Phone: 734.973.0137 ext 441
>
> Fax: 734.975.1248
>
> hermanj@hvpa.com <mailto:hermanj@hvpa.com>
>

So you want table3 to have the records from table1 except where they may
cancel out from table2?

In your example, why did only one of these records cancel out?
DATE     LN           MBRID     DS
1/1       A            1        30


If you have two records like above, its going to be hard to not cancel
them both out unless you can add some kind of identifier.

In the records for:
1/1       A           2        30

I see both of them cancel because there are two records in table2, correct?

Would it be ok if both 1/1, A, 1, 30 records canceled (ie do not get
copied to table3?)
Would it be ok if both 1/1, A, 2, 30.... Oh, wait!  forget the above.

Just noticed.  One table has +30 and the other has -30...   What if the
table3 record was a sum?  would that work?

-Andy

how about

select date, ln, mbrid, ds, (
   select sum(ds) from t2
   where t2.date >= t1.date and t2.date <= t1.date + '5 days'::interval
   and t1.ln = t2.ln
   and t1.mbrid = t2.mbrid)
from t1

That'll give you both the plus and minus (in two different columns), but
it might sum up the same row from table2 multiple times so I'm not sure
its correct.

And I'm not sure the date range is correct.

Another way to look at the same thing:

select date, ln, mbrid, dsplus - dsminus
from (
  select date, ln, mbrid, ds as dsplus, (
   select sum(ds) from t2
   where t2.date >= t1.date and t2.date <= t1.date + '5 days'::interval
   and t1.ln = t2.ln
   and t1.mbrid = t2.mbrid) as dsminus
  from t1
) as x
where dsplus - dsminus <> 0



Totally guessing here.

-Andy

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Andy Colson
Sent: Tuesday, February 21, 2012 1:37 PM
To: Jeff Herman
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Combining data from Temp Tables


how about

select date, ln, mbrid, ds, (
   select sum(ds) from t2
   where t2.date >= t1.date and t2.date <= t1.date + '5 days'::interval
   and t1.ln = t2.ln
   and t1.mbrid = t2.mbrid)
from t1

That'll give you both the plus and minus (in two different columns), but it
might sum up the same row from table2 multiple times so I'm not sure its
correct.

And I'm not sure the date range is correct.

Another way to look at the same thing:

select date, ln, mbrid, dsplus - dsminus from (
  select date, ln, mbrid, ds as dsplus, (
   select sum(ds) from t2
   where t2.date >= t1.date and t2.date <= t1.date + '5 days'::interval
   and t1.ln = t2.ln
   and t1.mbrid = t2.mbrid) as dsminus
  from t1
) as x
where dsplus - dsminus <> 0

Totally guessing here.

-Andy

-------------------------------------------------------------------

I am pretty certain this cannot be sufficiently solved via a declarative
statement; it requires procedural logic.

For each unmatched record on table 1 you compare all unmatched records on
table 2.  You pair the first one that matches and exclude the table 2 record
from all future comparisons.

I have done this before but my approach was to load all the unmatched
records into Java and perform the procedural logic there.  This can be done
in PL/PGSQL in a brute-force way and then, if performance is unacceptable,
you can try to add efficiencies or farm out the processing to a more full
featured programming language (one having Lists/Maps and/or Iterators).

Two possible situations to consider:

1) Does a record on table 1 (or table 2) ever have to match up with another
record on the same table (i.e., entry reversal)?
2) Is it ever possible for a record to be deleted?

Also consider what kind of meta-data you want to track in order to generate
a proper reconciliation report.  One common need is to know what the
reconciliation status looked like at some date in the past.  For instance on
the 5th of the month I want to know the exact reconciliation status of my
bank account.  To do this I have to ignore any "matching" entries that
occurred on or after the 1st of the current month (like checks clearing).

Again, the situation you are dealing with almost certainly requires a
procedural solution and so pure SQL is not going to work.  You need PL/PGSQL
(or some other embedded language) or, if you already have an application
server hooked into the database, a "query-process-update" routine coded and
run off the application server.

David J.

David,

Thanks for that.  There is always a feeling of relief and frustration when you learn that a language simply cannot do
whatyou are trying to get it to do.  You mentioned that this could be done by brute force with PL/PGSQL.  I do have
thisavailable, but am somewhat unfamiliar with it and am not sure where to begin.  I can answer the two situations you
broughtup. 
    1.  No, the records do not have to match up with records on the same table.  I created the two temp tables as a way
ofseparating the entries with the entry reversals, if that makes sense.  Now I am trying to reconcile the tables and
takeout the appropriate     records. 
    2.  It is not possible for a record to be deleted.

As for meta-data, I am not too concerned with that at the moment.  I am looking just to create a monthly "snapshot"
reportusing this data.  Because I am pulling this data from data feeds, I can control any "matching" entries that would
occurafter the first of the current month.  Thank you for considering these things in my problem. 

That being said, since I am mostly unfamiliar with PL/PGSQL could you (or anyone) provide an example of a solution?  I
amplaying with loops, but I am not sure I am on the right path. 

Thanks,

Jeff Herman
HVPA, Database Programmer
Phone: 734.973.0137 ext 441
Fax: 734.975.1248
hermanj@hvpa.com

-----Original Message-----
From: David Johnston [mailto:polobo@yahoo.com]
Sent: Tuesday, February 21, 2012 2:04 PM
To: 'Andy Colson'; Jeff Herman
Cc: pgsql-general@postgresql.org
Subject: RE: [GENERAL] Combining data from Temp Tables

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Andy Colson
Sent: Tuesday, February 21, 2012 1:37 PM
To: Jeff Herman
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Combining data from Temp Tables


how about

select date, ln, mbrid, ds, (
   select sum(ds) from t2
   where t2.date >= t1.date and t2.date <= t1.date + '5 days'::interval
   and t1.ln = t2.ln
   and t1.mbrid = t2.mbrid)
from t1

That'll give you both the plus and minus (in two different columns), but it might sum up the same row from table2
multipletimes so I'm not sure its correct. 

And I'm not sure the date range is correct.

Another way to look at the same thing:

select date, ln, mbrid, dsplus - dsminus from (
  select date, ln, mbrid, ds as dsplus, (
   select sum(ds) from t2
   where t2.date >= t1.date and t2.date <= t1.date + '5 days'::interval
   and t1.ln = t2.ln
   and t1.mbrid = t2.mbrid) as dsminus
  from t1
) as x
where dsplus - dsminus <> 0

Totally guessing here.

-Andy

-------------------------------------------------------------------

I am pretty certain this cannot be sufficiently solved via a declarative statement; it requires procedural logic.

For each unmatched record on table 1 you compare all unmatched records on table 2.  You pair the first one that matches
andexclude the table 2 record from all future comparisons. 

I have done this before but my approach was to load all the unmatched records into Java and perform the procedural
logicthere.  This can be done in PL/PGSQL in a brute-force way and then, if performance is unacceptable, you can try to
addefficiencies or farm out the processing to a more full featured programming language (one having Lists/Maps and/or
Iterators).

Two possible situations to consider:

1) Does a record on table 1 (or table 2) ever have to match up with another record on the same table (i.e., entry
reversal)?
2) Is it ever possible for a record to be deleted?

Also consider what kind of meta-data you want to track in order to generate a proper reconciliation report.  One common
needis to know what the reconciliation status looked like at some date in the past.  For instance on the 5th of the
monthI want to know the exact reconciliation status of my bank account.  To do this I have to ignore any "matching"
entriesthat occurred on or after the 1st of the current month (like checks clearing). 

Again, the situation you are dealing with almost certainly requires a procedural solution and so pure SQL is not going
towork.  You need PL/PGSQL (or some other embedded language) or, if you already have an application server hooked into
thedatabase, a "query-process-update" routine coded and run off the application server. 

David J.

>> Please follow the conventions of previous posters when adding a reply to
an existing posting.  If you are the first reply I would personally give
more lee-way but this particular community prefers bottom-posting.

-----Original Message-----
From: David Johnston [mailto:polobo@yahoo.com]
Sent: Tuesday, February 21, 2012 2:04 PM
To: 'Andy Colson'; Jeff Herman
Cc: pgsql-general@postgresql.org
Subject: RE: [GENERAL] Combining data from Temp Tables

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Andy Colson
Sent: Tuesday, February 21, 2012 1:37 PM
To: Jeff Herman
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Combining data from Temp Tables


how about

select date, ln, mbrid, ds, (
   select sum(ds) from t2
   where t2.date >= t1.date and t2.date <= t1.date + '5 days'::interval
   and t1.ln = t2.ln
   and t1.mbrid = t2.mbrid)
from t1

That'll give you both the plus and minus (in two different columns), but it
might sum up the same row from table2 multiple times so I'm not sure its
correct.

And I'm not sure the date range is correct.

Another way to look at the same thing:

select date, ln, mbrid, dsplus - dsminus from (
  select date, ln, mbrid, ds as dsplus, (
   select sum(ds) from t2
   where t2.date >= t1.date and t2.date <= t1.date + '5 days'::interval
   and t1.ln = t2.ln
   and t1.mbrid = t2.mbrid) as dsminus
  from t1
) as x
where dsplus - dsminus <> 0

Totally guessing here.

-Andy

-------------------------------------------------------------------

I am pretty certain this cannot be sufficiently solved via a declarative
statement; it requires procedural logic.

For each unmatched record on table 1 you compare all unmatched records on
table 2.  You pair the first one that matches and exclude the table 2 record
from all future comparisons.

I have done this before but my approach was to load all the unmatched
records into Java and perform the procedural logic there.  This can be done
in PL/PGSQL in a brute-force way and then, if performance is unacceptable,
you can try to add efficiencies or farm out the processing to a more full
featured programming language (one having Lists/Maps and/or Iterators).

Two possible situations to consider:

1) Does a record on table 1 (or table 2) ever have to match up with another
record on the same table (i.e., entry reversal)?
2) Is it ever possible for a record to be deleted?

Also consider what kind of meta-data you want to track in order to generate
a proper reconciliation report.  One common need is to know what the
reconciliation status looked like at some date in the past.  For instance on
the 5th of the month I want to know the exact reconciliation status of my
bank account.  To do this I have to ignore any "matching" entries that
occurred on or after the 1st of the current month (like checks clearing).

Again, the situation you are dealing with almost certainly requires a
procedural solution and so pure SQL is not going to work.  You need PL/PGSQL
(or some other embedded language) or, if you already have an application
server hooked into the database, a "query-process-update" routine coded and
run off the application server.

David J.



-----Original Message-----
From: Jeff Herman [mailto:hermanj@hvpa.com]
Sent: Tuesday, February 21, 2012 4:53 PM
To: David Johnston; 'Andy Colson'
Cc: pgsql-general@postgresql.org
Subject: RE: [GENERAL] Combining data from Temp Tables

David,

Thanks for that.  There is always a feeling of relief and frustration when
you learn that a language simply cannot do what you are trying to get it to
do.  You mentioned that this could be done by brute force with PL/PGSQL.  I
do have this available, but am somewhat unfamiliar with it and am not sure
where to begin.  I can answer the two situations you brought up.
    1.  No, the records do not have to match up with records on the same
table.  I created the two temp tables as a way of separating the entries
with the entry reversals, if that makes sense.  Now I am trying to reconcile
the tables and take out the appropriate     records.
    2.  It is not possible for a record to be deleted.

As for meta-data, I am not too concerned with that at the moment.  I am
looking just to create a monthly "snapshot" report using this data.  Because
I am pulling this data from data feeds, I can control any "matching" entries
that would occur after the first of the current month.  Thank you for
considering these things in my problem.

That being said, since I am mostly unfamiliar with PL/PGSQL could you (or
anyone) provide an example of a solution?  I am playing with loops, but I am
not sure I am on the right path.

---------------------------------------------------------------------

Jeff,

I don't have time to put together a full implementation in PL/PGSQL.  My
first thought would be to open a cursor on the unmatched table 1 entries.
For each record you query table 2 for possible matches and use LIMIT 1 to
ensure you only retrieve (at most) one match.  Insert the ID of the match
and the ID of the table 1 record into a table and move onto the next record
in the table 1 cursor.  When you are done you have a table containing all
the matches between tables 1 and 2 and you can:

SELECT * FROM table1 WHERE id1 NOT IN (SELECT table1_id FROM matchtable)
UNION ALL
SELECT * FROM table2 WHERE id2 NOT IN (SELECT table2_id FROM matchtable)

to find all of your unmatched entries.

Keep in mind concurrency issues and how you plan to handle the monthly
nature of the routine (processing and archiving).

This should get you started.

David J.

CREATE FUNCTION reconcile(...)
RETURNS TABLE (...)
AS $$
DECLARE tbl2_id varchar;
BEGIN
--PSUEDO CODE BELOW
FOR cursor1 IN (SELECT * FROM umatched_table1)

SELECT id2
FROM umatched_table2
WHERE (matching conditions)
LIMIT 1
INTO tbl2_id;

IF (tbl2_id IS NOT NULL) THEN
    INSERT INTO matchtable (tbl1_id, tbl2_id);
END IF;

NEXT;

RETURN QUERY
SELECT * FROM reconciliation_result;

RETURN;
END;
$$
LANGUAGE PLPGSQL
STRICT
VOLATILE;