SQL: copying data down

Tags
programming

 
Thread Tools Search this Thread
# 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:
Corona688 (11-30-2011) figaro (11-30-2011) Scott (11-29-2011)
# 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.

|
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 Programming 3 2 Weeks Ago 04:38 PM
Shell to SQL for data gksenthilkumar Shell Programming and Scripting 3 07-12-2015 02:36 PM
Copying data from USB CD Drive. videsh77 Red Hat 3 04-22-2014 03:32 PM
Copying part of a data file into another latsyrc UNIX for Dummies Questions & Answers 8 07-08-2013 07:38 AM
Copying data from one file server to another br1an Solaris 2 05-21-2013 08:36 PM
Copying data from files to directories newbie2010 Shell Programming and Scripting 2 05-17-2013 01:06 PM
Error while copying huge amount of data in aix samsungsamsung AIX 3 06-01-2011 05:34 PM
Doubt in C programming (copying data from one file to another) Lyric Programming 5 10-12-2010 03:56 PM
Importing data from PL/SQL then sending it through mail,HELP ME! Atrap Shell Programming and Scripting 1 10-07-2010 03:37 AM
How to use sql data file in unix csv file as input to an sql query from shell Nareshp Shell Programming and Scripting 2 11-09-2009 02:15 AM
Copying data from excel file 5ahen Shell Programming and Scripting 1 02-04-2009 10:39 AM
Compress the contents of a directory while copying data into it user1602 Shell Programming and Scripting 2 01-12-2009 08:53 PM
Copying/Routing data from the0 to eth1 and eth2 theVOID IP Networking 0 04-11-2008 05:19 AM
SQL Data with Spaces into Arrays mikem22 Shell Programming and Scripting 0 10-19-2007 06:49 AM
copying data to raw devices using 'dd' Neville UNIX for Advanced & Expert Users 4 03-23-2006 06:01 PM