Re: Need help building this query
От | Oliver d'Azevedo Christina |
---|---|
Тема | Re: Need help building this query |
Дата | |
Msg-id | 40E3F24438264205B10395EFCEE492AD@Moon обсуждение исходный текст |
Ответ на | Need help building this query (Rihad <rihad@stream.az>) |
Ответы |
Re: Need help building this query
|
Список | pgsql-sql |
For matching triples (foo, bar, baz) the date in table B shouldnt always be after any date in table A, as table B contains complete operations? Best, Oliver ----- Original Message ----- From: "Rihad" <rihad@stream.az> To: <pgsql-sql@postgresql.org> Sent: Thursday, June 21, 2012 6:48 PM Subject: [SQL] Need help building this query > Hi, folks. I currently need to join two tables that lack primary keys, and > columns used to distinguish each record can be duplicated. I need to build > statistics over the data in those tables. Consider this: > > > TableA: > row 1: foo: 123, bar: 456, baz: 789, amount: 10.99, date_of_op: date > row 2: foo: 123, bar: 456, baz: 789, amount: 10.99, date_of_op: date > row 3: foo: 123, bar: 456, baz: 789, amount: 10.99, date_of_op: date > > TableB: > row 1: foo: 123, bar: 456, baz: 789, amount: 10.99, date_of_op: date > > > Columns foo + bar + baz are used to distinguish a performed "operation": > TableA.date_of_op isn't, because it can lag behind TableB. > > Not all different "operations" are in table B. > Table B is just there so we know which "operations" are complete, so to > speak (happening under external means and not under any of my control). > > Now, for each operation (foo+bar+baz) in table A, only *one* row should be > matched in table B, because it only has one matching row there. > The other two in TableA should be considered unmatched. > > Now the query should be able to get count(*) and sum(amount) every day for > that day, considering that matched and unmatched operations should be > counted separately. The report would look something like this: > > TableA.date_of_op TableB.date_of_op > 2012-06-21 [empty] [count(*) and sum(amount) > of all data in TableA for this day unmatched in TableB] > 2012-06-21 2012-06-20 [count(*) and sum(amount) of > all data in TableA matched in TableB for the 20-th] > 2012-06-21 2012-06-19 [count(*) and sum(amount) of > all data in TableA matched in TableB for the 19-th] > > > Can this awkward thing be done in pure SQL, or I'd be better off using > programming for this? > > Thanks, I hope I could explain this. > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
В списке pgsql-sql по дате отправления: