Calculating changes in non-contiguous data

Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Calculating changes in non-contiguous data
# 1  
Old 12-06-2007
Calculating changes in non-contiguous data

I want to calculate day-to-day changes in a data set. In an ideal situation the following would suffice:
Date x
2007-01-01 5
2007-01-02 8
2007-01-03 4
2007-01-04 3
2007-01-05 10
2007-01-06 10
2007-01-07 2
2007-01-08 10
2007-01-09 1
2007-01-10 8

SELECT, (D1.x-D2.x)/D2.x AS change

This will not work however for non-contiguous dates. In other words , what would be the suggested SQL query for a data set as follows ?

2007-01-01 5
2007-01-02 8
2007-01-03 4
2007-01-04 3
2007-01-05 10
2007-01-08 10
2007-01-09 2
2007-01-10 10
2007-01-11 1
2007-01-12 8

where day 6 and 7 constitute a weekend for which no samples are present. My initial thought would be to have a temporary table with the following structure : id - date - x
where id is a contiguous autonumber and the resulting query would be:

SELECT, (TD1.x-TD2.x)/TD2.x AS change
FROM tempdata AS TD1 INNER JOIN tempdata AS TD2 ON;

But the question is, how should this table be constructed, particularly because not all databases support autonumbers? Any other approaches which are perhaps more straightforward?
# 2  
Old 12-07-2007
If using Oracle, then try the LAG analytic function. It provides access to more than one row of a table at the same time without a self join.
# 3  
Old 12-07-2007
Thank you for your response. Would appreciate an ANSI compliant method for portability purposes.
I have also thought about a procedural method, whereby the data is read into an array and sorted from there. Not too appealing either, prefer pure SQL.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Grouping and Calculating

Hi All, I want to read the input file and store the output in the Output file. I pasted the sample Input and Output file below. Help me with this. Input file ================================= ITEM1 AAAAA 1 ITEM1 BBBBB 1 ITEM1 CCCCC 1 ITEM2 AAAAA 5 ITEM2 CCCCC 4... (1 Reply)
Discussion started by: humaemo
1 Replies

2. UNIX for Dummies Questions & Answers

Calculating average

Hi I have file like below 111,victor,48,12,36 342,Peter,54,58,30 476,Scott,25,36,48 567,Patty,74,17,95 I have written below code to calcualte avereage for every id Victor = 48+12+36/3 #!/bin/ksh /usr/xpg4/bin/awk ' BEGIN {FS=","} {sum=0; n=0;i=3 (1 Reply)
Discussion started by: stew
1 Replies

3. Shell Programming and Scripting

Calculating sum

Hi all, I have the following code in a shell script. Code: if then echo "##" echo "##" echo "##################################" for fn in `find "$1" -maxdepth 1 -iname \*"$2"* -type f` do echo "$fn" ... (2 Replies)
Discussion started by: naveendronavall
2 Replies

4. Shell Programming and Scripting

Finding contiguous numbers in a list but with a gap number tolerance

Dear all, I have a imput file like this imput scaffold_0 10558458 10558459 1.8 scaffold_0 10558464 10558465 1.75 scaffold_0 10558467 10558468 1.8 scaffold_0 10558468 10558469 1.71428571428571 scaffold_0 10558469... (5 Replies)
Discussion started by: valente
5 Replies

5. Shell Programming and Scripting

Calculating the epoch time from standard time using awk and calculating the duration

Hi All, I have the following time stamp data in 2 columns Date TimeStamp(also with milliseconds) 05/23/2012 08:30:11.250 05/23/2012 08:30:15.500 05/23/2012 08:31.15.500 . . etc From this data I need the following output. 0.00( row1-row1 in seconds) 04.25( row2-row1 in... (5 Replies)
Discussion started by: ks_reddy
5 Replies

6. Shell Programming and Scripting

Calculating using date

I need to help to calculating using date in a script. One application is licensed by date, some month at a time. I can read the date from system and get an output like this: echo $status 6A34 System4 01.01.11-31.01.11 My goal is to use license date 31.01.11 and subtract todays date... (7 Replies)
Discussion started by: Jotne
7 Replies

7. UNIX for Dummies Questions & Answers

Calculating average

Hi, i have 12 float variables in a bash file and i want to calculate the average of them. Can any body help? (6 Replies)
Discussion started by: limadario
6 Replies

8. Shell Programming and Scripting

calculating in MB

hi all, have got a ksh script which tries to monitor memory usage of app servers. i do a ps -0 rss -p <PID> to get the memory size in KB but when i divide by 1024 to convert to MB i dont know how to round it up ?? thanks in advance. (3 Replies)
Discussion started by: cesarNZ
3 Replies

9. Filesystems, Disks and Memory

will a new file be contiguous on my drive?

Say I am creating a new file on UNIX, how can I find out if it is contiguous on disk? thanks (1 Reply)
Discussion started by: JamesByars
1 Replies

10. Shell Programming and Scripting

calculating a number

Hello all :) I need some help; I'm running the sp_spaceused command on various tables and saving the output to a file. So, I have an input file that has 3 rows - each row has 7 columns. I would like to 1) sort the file on the 4th column, 2) take the 4th column in the first row and add 25% to... (2 Replies)
Discussion started by: stonemonolith
2 Replies
Login or Register to Ask a Question