SQL: copying data down


Login or Register to Reply

 
Thread Tools Search this Thread
# 1  
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  
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  
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  
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  
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  
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 05:39 PM..
# 7  
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 Reply

|
Thread Tools Search this Thread
Search this Thread:
Advanced Search

More UNIX and Linux Forum Topics You Might Find Helpful
SQL: copying data up
figaro
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...... Programming
3
Programming
Copying data from one file server to another
br1an
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 ...... Solaris
2
Solaris
Copying data from files to directories
newbie2010
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...... Shell Programming and Scripting
2
Shell Programming and Scripting
How to use sql data file in unix csv file as input to an sql query from shell
Nareshp
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...... Shell Programming and Scripting
2
Shell Programming and Scripting
copying data to raw devices using 'dd'
Neville
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...... UNIX for Advanced & Expert Users
4
UNIX for Advanced & Expert Users