Обсуждение: Query design assistance - getting daily totals
I have a table of account balances as at the end of a working day and want to from that, calculate daily total figures. Eg, let's say I have a table structure of: year_id integer month_id integer working_day integer account integer account_balance numeric(19,4) Example data might be something like 2007,12,1,1,100.00 2007,12,2,1,200.00 2007,12,3,1,250.00 2007,12,4,1,500.00 2007,12,5,1,575.00 I want to construct a query that will give me the daily balances from this information, so I would be presented with something like: 2007,12,1,1,100.00 2007,12,2,1,100.00 2007,12,3,1,50.00 2007,12,4,1,250.00 2007,12,5,1,75.00 I figure there's a couple of ways I could do it... Firstly, build a complicated nested select where the lower level gets the main data, then the outer select joins it on itself where the working_day is equal to the working_day-1 from the nested query and then wrap that in another select that calculates the difference in the account_balance column from both. The second option I think would be to create a function whereby I pass it the primary key fields (year_id,month_id,working_day,account) and have it do two selects and work out the difference. I suspect the second option would be more efficient than the first, and probably easier to implement since it would be easier to handle cross-month boundaries, i.e. day 1's daily total will be the amount on that day minus the amount of the final day in the previous month - but does anyone have any alternate suggestions that would be better still? Cheers, Paul. -- Paul Lambert Database Administrator AutoLedgers
am Wed, dem 12.12.2007, um 10:34:35 +0900 mailte Paul Lambert folgendes: > I have a table of account balances as at the end of a working day and > want to from that, calculate daily total figures. > > Eg, let's say I have a table structure of: > year_id integer > month_id integer > working_day integer Why this broken data types? We have date and timestamp[tz]. > I suspect the second option would be more efficient than the first, and > probably easier to implement since it would be easier to handle > cross-month boundaries, i.e. day 1's daily total will be the amount on > that day minus the amount of the final day in the previous month - but > does anyone have any alternate suggestions that would be better still? Yes, i would also write a similar function. And if you have proper datatypes it would be simpler to calculate the previous date and you can use a proper index on the date column. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
A. Kretschmer wrote: > am Wed, dem 12.12.2007, um 10:34:35 +0900 mailte Paul Lambert folgendes: >> year_id integer >> month_id integer >> working_day integer > > Why this broken data types? We have date and timestamp[tz]. > > It's a financial application which needs to work using a concept of 'financial periods' which may not necessarily correspond to calendar months and it's much easier to manage in this way than it is to merge it all together using a date field. Eg, 1st January may actually be the 15th 'working day' of the 9th 'financial period' - however looking at just a date of jan-1 there is no way of knowing this and it's the periods that matter more so than the actual date. I've given the function method a try and it looks to work efficiently enough. P. -- Paul Lambert Database Administrator AutoLedgers - A Reynolds & Reynolds Company
am Wed, dem 12.12.2007, um 15:39:48 +0900 mailte Paul Lambert folgendes: > A. Kretschmer wrote: > >am Wed, dem 12.12.2007, um 10:34:35 +0900 mailte Paul Lambert folgendes: > >>year_id integer > >>month_id integer > >>working_day integer > > > >Why this broken data types? We have date and timestamp[tz]. > > > > > > It's a financial application which needs to work using a concept of > 'financial periods' which may not necessarily correspond to calendar > months and it's much easier to manage in this way than it is to merge it Ahh, thanks for the explanation. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
On Dec 12, 2007 12:39 AM, Paul Lambert <paul.lambert@reynolds.com.au> wrote: > A. Kretschmer wrote: > > am Wed, dem 12.12.2007, um 10:34:35 +0900 mailte Paul Lambert folgendes: > >> year_id integer > >> month_id integer > >> working_day integer > > > > Why this broken data types? We have date and timestamp[tz]. > > > > > > It's a financial application which needs to work using a concept of > 'financial periods' which may not necessarily correspond to calendar > months and it's much easier to manage in this way than it is to merge it > all together using a date field. Eg, 1st January may actually be the > 15th 'working day' of the 9th 'financial period' - however looking at > just a date of jan-1 there is no way of knowing this and it's the > periods that matter more so than the actual date. I'm not sure that really justifies your method though. Not saying "you're doing it wrong" so much as I'm not sure the way you're doing it makes it any easier to keep track of certain periods. Any method you would use to pick rows with the disjointed dates could be applied to date and / or timestamp types as easily, and with some functional indexes on the date / timestamp columns you could easily select periods quickly as well. Just saying.
On Dec 12, 2007 1:39 AM, Paul Lambert <paul.lambert@reynolds.com.au> wrote: > It's a financial application which needs to work using a concept of > 'financial periods' which may not necessarily correspond to calendar > months and it's much easier to manage in this way than it is to merge it > all together using a date field. Eg, 1st January may actually be the > 15th 'working day' of the 9th 'financial period' - however looking at > just a date of jan-1 there is no way of knowing this and it's the > periods that matter more so than the actual date. I think what you need is a Calendar Table to "map" actual dates to "buckets" e.g. 'financial periods', etc. See: http://codeinet.blogspot.com/2006/08/auxiliary-calendar-table-for-sql.html
Hi, Rodrigo is exactly right in my opinion. To provide a little more info on this calendar or day dimension idea.. You can create, for example, a time table dimension which stores every day of every year as a unique record (for as far into the future as you need). You can then associate various attributes to each day, depending on your business needs like so: id|datetime|is_business_day|is_weekday|is_fed_holiday Of course it's not normalized but that's the point. You then just store the id in various places and it's easy to join back to this table and figure out if a particular day has an attribute you're interested in (or you can find the id's for all the days which have a particular attribute for a given date range - to go the other direction, for example). You can get more on this type of thinking from the most excellent resource by Ralph Kimball "The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)" - this book did more to open my eyes to alternative to traditional "normalized" modeling than anything else. It also made me feel less guilty about building certain non-normal structures. :) I hope that's helpful.. Steve At 12:21 PM 12/13/2007, pgsql-sql-owner@postgresql.org wrote: >Date: Wed, 12 Dec 2007 14:53:08 -0500 >From: "Rodrigo De León" <rdeleonp@gmail.com> >To: "Paul Lambert" <paul.lambert@reynolds.com.au> >Cc: pgsql-sql@postgresql.org >Subject: Re: Query design assistance - getting daily totals >Message-ID: ><a55915760712121153x5c9a10a1s89c737a44e4eb149@mail.gmail.com> > >On Dec 12, 2007 1:39 AM, Paul Lambert <paul.lambert@reynolds.com.au> >wrote: > > It's a financial application which needs to work using a concept of > > 'financial periods' which may not necessarily correspond to > calendar > > months and it's much easier to manage in this way than it is to > merge it > > all together using a date field. Eg, 1st January may actually be > the > > 15th 'working day' of the 9th 'financial period' - however looking > at > > just a date of jan-1 there is no way of knowing this and it's the > > periods that matter more so than the actual date. > >I think what you need is a Calendar Table to "map" actual dates to >"buckets" e.g. 'financial periods', etc. See: > >http://codeinet.blogspot.com/2006/08/auxiliary-calendar-table-for-sql.html