Re: Is it possible to have an array in a table? (again)
Date: Tue, 01 Sep 2009 08:09:02 +0200
Message-ID: <7g3s40F2m0cqlU1_at_mid.individual.net>
On 01.09.2009 06:02, Ramon F Herrera wrote:
> On Aug 22, 4:07 pm, Ramon F Herrera <ra..._at_conexus.net> wrote:
>> Can a table contain data arranged in two dimensions, and accessible >> through a pair of integer subscripts? >> >> I can live with 1-dimensional arrays, too. >> >> TIA, >> >> -RFH
>
> Thanks to everyone for their help...
>
> I would like to provide a specific example of what I am trying to
> accomplish. Let's say I run a circus and every day I get the totals
> sold in a given week.
>
> Categories are: ticket sales, cotton candy, souvenirs, etc.
>
> My application needs to display the sales like this:
>
> Ticket Sales
> 8/22/2009: $8492.10
> 8/15/2009: $9024.31
> 8/8/2009: $7893.03
> 8/1/2009: $8945.67
As far as I can see you just want to condense daily data into weekly data. So that would make for me
category, date, amount
index on: category, date (maybe even IOT)
and then
select first_day_in_week(date), sum(amount) amount
from your_table
where cat = 'Ticket Sales'
and date >= first_day_in_week(sysdate() - 6 weeks) -- note: just informal
group by first_day_in_week(date)
sort by first_day_in_week(date)
> People keep on recommending the SQL paradigm, but the above looks to
> me like something that must be handled by arrays.
Nature of usenet is that you might not always get the answer you expected. I don't see any advantage of using arrays here - in fact it will likely hinder portability.
> I need to display the 6 most recent weeks and I keep on pushing the
> most recent sales figures at the top, while I roll down and eventually
> drop the oldest data. This kind of think MUST be handled with an
> index, such as week-1 or week-2.
>
> If I followed the relational model, I would need to hardcode records
> such as:
>
> CURRENT_WEEK
> WEEK_MINUS_1
> WEEK_MINUS_2
> etc.
No, you don't. You can use date and time arithmetic.
> That would be a nightmare to code.
>
> So, the question is how to implement the records (with arrays?) for a
> rolling scenario like the above described.
You somehow seem to be hooked on the idea that your tabular data must change with every week. Instead you should store the original dates and use math to determine the records you need.
Kind regards
robert
-- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/Received on Tue Sep 01 2009 - 01:09:02 CDT