Is it possible to temporarily duplicate and change rows on the fly in an SQL SELECT query?

Question

Is it possible to temporarily duplicate and change rows on the fly in an SQL SELECT query?

I just got a new data source for my application that inserts data into the Derby database only when it changes. As a rule, the missing data is beautiful - I draw a linear diagram with the data (value over time), and I simply draw a line between two points, extrapolating the expected value at any given point. The problem is that since missing data in this case means “draw a straight line”, the graph would be wrong if I did this.

I can fix this in two ways: I could create a new class that processes the missing data in different ways (which can be difficult due to the prefuse method, the drawing library I use processes the drawing), or I could duplicate the lines, leaving the y value the same when changing the x value in each row. I could do this in Java, which integrates the database and the renderer, or I can modify SQL.

My question, given a result similar to the one below:

+-------+---------------------+
| value | received            |
+-------+---------------------+
|     7 | 2000-01-01 08:00:00 |
|    10 | 2000-01-01 08:00:05 |
|    11 | 2000-01-01 08:00:07 |
|     2 | 2000-01-01 08:00:13 |
|     4 | 2000-01-01 08:00:16 |
+-------+---------------------+

Assuming I request it at 8:00:20, how can I make it look like the following using SQL? Basically, I duplicate a line every second until it is already done. receivedunique for all purposes and goals (this is not so, but it will be associated with the proposal WHEREin the request).

+-------+---------------------+
| value | received            |
+-------+---------------------+
|     7 | 2000-01-01 08:00:00 |
|     7 | 2000-01-01 08:00:01 |
|     7 | 2000-01-01 08:00:02 |
|     7 | 2000-01-01 08:00:03 |
|     7 | 2000-01-01 08:00:04 |
|    10 | 2000-01-01 08:00:05 |
|    10 | 2000-01-01 08:00:06 |
|    11 | 2000-01-01 08:00:07 |
|    11 | 2000-01-01 08:00:08 |
|    11 | 2000-01-01 08:00:09 |
|    11 | 2000-01-01 08:00:10 |
|    11 | 2000-01-01 08:00:11 |
|    11 | 2000-01-01 08:00:12 |
|     2 | 2000-01-01 08:00:13 |
|     2 | 2000-01-01 08:00:14 |
|     2 | 2000-01-01 08:00:15 |
|     4 | 2000-01-01 08:00:16 |
|     4 | 2000-01-01 08:00:17 |
|     4 | 2000-01-01 08:00:18 |
|     4 | 2000-01-01 08:00:19 |
|     4 | 2000-01-01 08:00:20 |
+-------+---------------------+

Thank you for your help.

+3

sql derby

Samir Talwar 15 . '09 19:13

9

- SQL . :

a)

b) ( ) 1 , , .

b) ( SQL Server)

, 24 . , dttm . .

select dateadd(minute,stepMinutes,'2000-01-01 08:00') received,
(select top 1 value from table where received <= 
dateadd(minute,dttm,'2000-01-01 08:00') 
order by received desc) value
from intervals

+3

tekBlues 15 . '09 19:27

, vars , , , , .

SQL , , , , / .

+1

Kevin K 15 . '09 20:06

, , . , , , , .

, . SQL Server , , datetime. .

, SQL Server. Subselect aliased r - , .

select r.received,
isnull(d.value,(select top 1 data.value from data where data.received < r.received order by data.received desc)) as x
from (
    select cast('2000-01-01 08:00:00' as datetime) received
    union all
    select cast('2000-01-01 08:00:01' as datetime)
    union all
    select cast('2000-01-01 08:00:02' as datetime)
    union all
    select cast('2000-01-01 08:00:03' as datetime)
    union all
    select cast('2000-01-01 08:00:04' as datetime)
    union all
    select cast('2000-01-01 08:00:05' as datetime)
    union all
    select cast('2000-01-01 08:00:06' as datetime)
    union all
    select cast('2000-01-01 08:00:07' as datetime)
    union all
    select cast('2000-01-01 08:00:08' as datetime)
    union all
    select cast('2000-01-01 08:00:09' as datetime)
    union all
    select cast('2000-01-01 08:00:10' as datetime)
    union all
    select cast('2000-01-01 08:00:11' as datetime)
    union all
    select cast('2000-01-01 08:00:12' as datetime)
    union all
    select cast('2000-01-01 08:00:13' as datetime)
    union all
    select cast('2000-01-01 08:00:14' as datetime)
    union all
    select cast('2000-01-01 08:00:15' as datetime)
    union all
    select cast('2000-01-01 08:00:16' as datetime)
    union all
    select cast('2000-01-01 08:00:17' as datetime)
    union all
    select cast('2000-01-01 08:00:18' as datetime)
    union all
    select cast('2000-01-01 08:00:19' as datetime)
    union all
    select cast('2000-01-01 08:00:20' as datetime)
) r
left outer join Data d on r.received = d.received

0

RedFilter 15 . '09 19:22

, , , , , /if.

, .

, , , .

, OLAP.

0

dkretz 15 . '09 19:28

(, ):

Create Table Minutes(Value DateTime Not Null)
Go

Declare @D DateTime
Set @D = '1/1/2000'

While (Year(@D) < 2002)
Begin
  Insert Into Minutes(Value) Values(@D)
  Set @D = DateAdd(Minute, 1, @D)
End
Go


Create Clustered Index IX_Minutes On Minutes(Value)
Go

:

Select 
  Received = Minutes.Value,
  Value = (Select Top 1 Data.Value
           From Data
           Where Data.Received <= Minutes.Received
           Order By Data.Received Desc)
From
  Minutes
Where
  Minutes.Value Between @Start And @End

0

Stu 15 . '09 19:29

SQL/ - . , , , , .

0

Jonas Elfström 15 . '09 19:40

SQL Server, . , Apache Derby sql.

Usage: EXEC ElaboratedData '2000-01-01 08:00:00','2000-01-01 08:00:20'

CREATE PROCEDURE [dbo].[ElaboratedData]
  @StartDate DATETIME,
  @EndDate DATETIME
AS
  --if not a valid interval, just quit
  IF @EndDate<=@StartDate BEGIN
    SELECT 0;    
    RETURN;
  END;

  /*
  Store the value of 1 second locally, for readability
  --*/
  DECLARE @OneSecond FLOAT;
  SET @OneSecond = (1.00000000/86400.00000000);

  /*
  create a temp table w/the same structure as the real table.
  --*/
  CREATE TABLE #SecondIntervals(TSTAMP DATETIME, DATAPT INT);

  /*
  For each second in the interval, check to see if we have a known value.
  If we do, then use that.  If not, make one up.
  --*/ 
  DECLARE @CurrentSecond DATETIME; 
  SET @CurrentSecond = @StartDate;
  WHILE @CurrentSecond <= @EndDate BEGIN
    DECLARE @KnownValue INT;

    SELECT @KnownValue=DATAPT
    FROM TESTME
    WHERE TSTAMP = @CurrentSecond;

    IF (0 = ISNULL(@KnownValue,0)) BEGIN
      --ok, we have to make up a fake value
      DECLARE @MadeUpValue INT;
      /*
      *******Put whatever logic you want to make up a fake value here
      --*/
      SET @MadeUpValue = 99;

      INSERT INTO #SecondIntervals(
        TSTAMP
       ,DATAPT
      )
      VALUES(
        @CurrentSecond
       ,@MadeUpValue
      );
    END;  --if we had to make up a value
    SET @CurrentSecond = @CurrentSecond + @OneSecond;
  END;  --while looking thru our values

  --finally, return our generated values + real values
  SELECT TSTAMP, DATAPT FROM #SecondIntervals
  UNION ALL
  SELECT TSTAMP, DATAPT FROM TESTME
  ORDER BY TSTAMP;
GO

0

JosephStyons 15 . '09 20:46

, SQL Anthony Mollinaro, 9. , " " ( 278-281), , . , , . Derby , , , , ( , PostgreSQL MySQL , ).

0

J. Polfer 15 . '09 21:09

LPCRoy · Accepted Answer · 2009-06-15T19:59:42+0000

, . ? , , ... , . 5 , , .

+-------+---------------------+
| value | received            |
+-------+---------------------+
|     7 | 2000-01-01 08:00:00 |
|     7 | 2000-01-01 08:00:04 |
|    10 | 2000-01-01 08:00:05 |
|    10 | 2000-01-01 08:00:06 |
|    11 | 2000-01-01 08:00:07 |
|    11 | 2000-01-01 08:00:12 |
|     2 | 2000-01-01 08:00:13 |
|     2 | 2000-01-01 08:00:15 |
|     4 | 2000-01-01 08:00:16 |
|     4 | 2000-01-01 08:00:20D |
+-------+---------------------+

:

SELECT * FROM
(SELECT * from TimeTable as t1
UNION
SELECT t2.value, dateadd(ms, -5, t2.received)
from ( Select t3.value, (select top 1 t4.received  
                         from TimeTable t4 
                         where t4.received > t3.received
                         order by t4.received asc) as received
from TimeTable t3) as t2
UNION
SELECT top 1 t6.value, GETDATE()
from TimeTable t6
order by t6.received desc
) as t5
where received IS NOT NULL
order by t5.received

, , .

Is it possible to temporarily duplicate and change rows on the fly in an SQL SELECT query?

More articles: