SQL: copying data down


 
Thread Tools Search this Thread
Top Forums Programming SQL: copying data down
# 1  
Old 11-29-2011
SQL: copying data down

I have a series of observations of which one column is sometimes missing (zero):

Code:
date	temp	delta
1977	284.54	29.84
1978	149.82	0
1979	320.71	28.45
1980	176.76	0
1981	854.65	0
1984	817.65	0
1985	990.58	27.98
1986	410.21	0
1987	405.93	0
1988	482.9	0

What I would like to achieve is a SQL selection whereby the non-zero values are copied down, in other words, to achieve the following:

Code:
date	temp	delta
1977	284.54	29.84
1978	149.82	29.84
1979	320.71	28.45
1980	176.76	28.45
1981	854.65	28.45
1984	817.65	28.45
1985	990.58	27.98
1986	410.21	27.98
1987	405.93	27.98
1988	482.9	27.98

Is there a standard way to achieve this without having to resort to creating a procedural SQL script?

Code:
DROP TABLE IF EXISTS `temperatures`;
CREATE TABLE `temperatures` (
  `date` integer,
  `temp` double,
  `delta` double
) ENGINE=MyISAM;
INSERT INTO `temperatures` VALUES (1977,284.54,29.84), (1978,149.82,''), 
(1979,320.71,28.45), (1980,176.76,''), (1981,854.65,''), 
(1984,817.65,''), (1985,990.58,27.98), (1986,410.21,''), 
(1987,405.93,''), (1988,482.9,''), (1991,269.02,'');

# 2  
Old 11-29-2011
Don't know a way to get the last row inside an SQL statement, no. It'd be extremely easy in anything but SQL. Can you feed it through awk?

Code:
awk 'BEGIN { OFS=FS="\t" } NR>1 { if($3) { L=$3; } else { $3=L } } 1' < data

date    temp    delta
1977    284.54  29.84
1978    149.82  29.84
1979    320.71  28.45
1980    176.76  28.45
1981    854.65  28.45
1984    817.65  28.45
1985    990.58  27.98
1986    410.21  27.98
1987    405.93  27.98
1988    482.9   27.98


$

# 3  
Old 11-29-2011
Thank you for your answer. Actually we have a solution in C++, which is highly performant. The only problem is, we need to do this every time this data is part of a calculation, and therefore redundant (unless someone sits down and actually creates an UPDATE statement too).
# 4  
Old 11-29-2011
Code:
mysql> select
    ->   date,
    ->   temp,
    ->   case
    ->     when delta > 0 && @d := delta then delta
    ->     else @d
    ->   end as delta
    -> from
    ->   temperatures
    -> order by
    -> date;
+------+--------+-------+
| date | temp   | delta |
+------+--------+-------+
| 1977 | 284.54 | 29.84 |
| 1978 | 149.82 | 29.84 |
| 1979 | 320.71 | 28.45 |
| 1980 | 176.76 | 28.45 |
| 1981 | 854.65 | 28.45 |
| 1984 | 817.65 | 28.45 |
| 1985 | 990.58 | 27.98 |
| 1986 | 410.21 | 27.98 |
| 1987 | 405.93 | 27.98 |
| 1988 |  482.9 | 27.98 |
| 1991 | 269.02 | 27.98 |
+------+--------+-------+
11 rows in set (0.00 sec)

These 3 Users Gave Thanks to radoulov For This Post:
# 5  
Old 11-30-2011
Ran a few tests on this and the result is immediate for a few hundred rows and 2.4 seconds on a table with 200.000 rows, so suitably fast for our purposes.
# 6  
Old 11-30-2011
If you need to improve the performance of the above statement you may try to index the date column
(in certain cases, for example Oracle (not sure for MySQL, you may check wit explain <your_statement_here>),
may try to use the index in order to avoid the costly sorting operation -
because the optimizer is aware that the data in the index is already sorted).

BTW: thanks for providing the necessary code in order to set up a test case!

Last edited by radoulov; 11-30-2011 at 06:39 PM..
# 7  
Old 11-30-2011
Thank you again for your contribution on this. Just ran the statement again on a slightly faster machine and achieved 0.41 seconds without indexes and 0.98 seconds with indexes. So the indexing strategy clearly needs some tweaking, but the overall approach is very promising.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Programming

SQL: copying data up

I need to fix an SQL statement in MySQL that should calculate a field using values from two of the columns and I prefer to do this using set-based programming, ie not procedural. What needs to happen is that in a separate column called "delta" the value of "level" is copied depending on whether... (3 Replies)
Discussion started by: figaro
3 Replies

2. Red Hat

Copying data from USB CD Drive.

I need to perform operation as captioned. I found out USB CD Drive is attached, by running 'lsusb' command. I am trying to identify mechanism by which I could mount this USB CD drive to disk. After which I expect to copy the contents. Could anyone indicate how this could be performed ? (3 Replies)
Discussion started by: videsh77
3 Replies

3. UNIX for Dummies Questions & Answers

Copying part of a data file into another

Hi, I have a large number of data files each containing simple integers from 1 to around 25000 in ascending order. However, they are not in a specific progression; some numbers are missing in each file. For ex. datfile1 may have the numbers in order 1 2 4 6 7 8 12 ... 24996 24999 while datfile2... (8 Replies)
Discussion started by: latsyrc
8 Replies

4. Solaris

Copying data from one file server to another

Hello people, I have a question regarding transferring data from one file server to another. The server is a Solaris 9 box The old file server is connected via Ethernet cable, and the new file server we are switching is a Fiber channel. can I use the dd if=server:/app1 of=server2:/app1 ... (2 Replies)
Discussion started by: br1an
2 Replies

5. Shell Programming and Scripting

Copying data from files to directories

I have the following that I'd like to do: 1. I have split a file into separate files that I placed into the /tmp directory. These files are named F1 F2 F3 F4. 2. In addition, I have several directories which are alphabetized as dira dirb dirc dird. 3. I'd like to be able to copy F1 F2 F3 F4... (2 Replies)
Discussion started by: newbie2010
2 Replies

6. Programming

Doubt in C programming (copying data from one file to another)

Hello, i'm new to the forum and so am i to C programming. Recently i've gotten a task to create a program that will read an existing .bin file and copy the data to a non existing (so i have to create it) .txt file (some type of conversion) Now, i now how to put the arguments, opening and... (5 Replies)
Discussion started by: Lyric
5 Replies

7. Shell Programming and Scripting

How to use sql data file in unix csv file as input to an sql query from shell

Hi , I used the below script to get the sql data into csv file using unix scripting. I m getting the output into an output file but the output file is not displayed in a separe columns . #!/bin/ksh export FILE_PATH=/maav/home/xyz/abc/ rm $FILE_PATH/sample.csv sqlplus -s... (2 Replies)
Discussion started by: Nareshp
2 Replies

8. Shell Programming and Scripting

Copying data from excel file

Hii friends, I am a newbie to unix/shell scripting and got stuck in implementing a functionality.Dear experts,kindly spare some time to bring me out of dark pit :confused:.. My requirement is somewhat wierd,let me explain what i have and what i need to do... 1) there are several excel... (1 Reply)
Discussion started by: 5ahen
1 Replies

9. Shell Programming and Scripting

Compress the contents of a directory while copying data into it

Hi guys I have a need to compress the contents of a directory while I am copying data into it. I was able to do this when it was only one file by doing as below: STEP1: mknod myfile p STEP2: chmod 777 myfile STEP3: compress -v < myfile > myfile.Z & STEP4: cp -p xyz_file myfile... (2 Replies)
Discussion started by: user1602
2 Replies

10. UNIX for Advanced & Expert Users

copying data to raw devices using 'dd'

Hello all, I'm new here, so this information may exist elsewhere on this forum. If so, please point me in the right direction. Here's the problem. I'm trying to migrate Oracle data from an HP system to a Sun system using a raw device as a 'bridge' between the two systems. Both machines... (4 Replies)
Discussion started by: Neville
4 Replies
Login or Register to Ask a Question