Query Tuning in Dashdb

Question

id  timestamp        status
167 6/28/2016 8:05:58 PM    0
167 6/28/2016 8:06:02 PM    0
167 6/28/2016 8:06:05 PM    0
167 6/28/2016 8:08:06 PM    0
167 6/28/2016 8:08:10 PM    0
167 6/28/2016 8:08:13 PM    3
167 6/28/2016 8:08:17 PM    3
167 6/28/2016 8:10:41 PM    3
167 6/28/2016 8:10:45 PM    3
167 6/28/2016 8:10:48 PM    3
167 6/28/2016 8:10:51 PM    0
167 6/28/2016 8:10:55 PM    0
167 6/28/2016 8:26:40 PM    0
167 6/28/2016 8:26:44 PM    0
167 6/28/2016 8:26:47 PM    3
167 6/28/2016 8:26:50 PM    3
167 6/28/2016 8:29:19 PM    3
167 6/28/2016 8:29:23 PM    3
167 6/28/2016 8:29:26 PM    0
167 6/28/2016 8:29:29 PM    0
167 6/28/2016 8:30:33 PM    0
167 6/28/2016 8:30:37 PM    0
167 6/28/2016 8:30:40 PM    3
167 6/28/2016 8:30:44 PM    3
167 6/28/2016 8:33:11 PM    3
167 6/28/2016 8:33:15 PM    3
167 6/28/2016 8:33:18 PM    0
167 6/28/2016 8:33:21 PM    0
167 6/28/2016 8:33:45 PM    0
167 6/28/2016 8:33:48 PM    0
167 6/28/2016 8:33:52 PM    0
167 6/28/2016 8:33:55 PM    3
167 6/28/2016 8:33:58 PM    3
167 6/28/2016 8:34:59 PM    3
167 6/28/2016 8:35:03 PM    3

The above data is from one of our IoT Systems and it basically tells in what state a machine is in w.r.t the timestamp with 0 being Stop and 3 being running.

I want to calculate parts produced in hourly basis in one hour and for this the logic is to check if the machine status changes from 3 to 0 then it has produced a aprt.

I have written then below query, and it is giving the intended result. The query works fine if we execute it for say 3-5 machines , if the same is executed against say 100 machines in a 24 hour period it would take a long time. For now the query takes 14 minutes per device for 1 hour data.

Infra used -- DashDB in bluemix, data to this db is pushed from cloudant again a bluemix service.

====
select a.runstatus,a.runcount ,b.runstatus,b.runcount from (SELECT  a.run_status runstatus, count(a.run_status) runcount
FROM machinedata a where a.run_status <>  ( SELECT b.run_status
        FROM machinedata  b
        WHERE a.machine_id = b.machine_id
          AND a.timestamp > b.timestamp  
          ORDER BY b.timestamp DESC
        LIMIT 1
      ) and a.machine_id = 167 and  a.run_status=0 AND a.`timestamp` BETWEEN '2016-06-28 20:00:01'
                                   AND '2016-06-28 20:59:59' group by a.run_status) as a,
      (SELECT  a.run_status runstatus, count(a.run_status) runcount
FROM machinedata a where a.run_status <>  ( SELECT b.run_status
        FROM machinedata  b
        WHERE a.machine_id = b.machine_id
          AND a.timestamp > b.timestamp  
          ORDER BY b.timestamp DESC
        LIMIT 1
      ) and a.machine_id = 167 and  a.run_status=3 AND a.`timestamp` BETWEEN '2016-06-28 20:00:01'
                                   AND '2016-06-28 20:59:59' group by a.run_status) as b

Torsten Steinbach · Answer 1 · 2017-02-27T13:32:22.997

0

This one will do the job:

select timestamp part_produced_time, hour(timestamp) part_produced_hour
       from (select *, first_value(status) over 
                         (order by timestamp asc
                          rows between 1 preceding and current row)
                       as prev_status
             from machinedata
            )
       where status=0 and prev_status=3

edited Feb 27 '17 at 13:32

answered Feb 27 '17 at 12:20

Torsten Steinbach

391
2
5

@LohithKumar I verified it again and it works fine as I pasted it. – Torsten Steinbach Mar 01 '17 at 07:26
Here is the table that I created for my test ```CREATE TABLE MACHINEDATA (ID INT, TIMESTAMP TIMESTAMP, STATUS INT) ``` – Torsten Steinbach Mar 01 '17 at 07:27
Please send the full query – Lohith Kumar Mar 02 '17 at 07:36
I am getting null current row Query is --- – Lohith Kumar Mar 02 '17 at 12:27
```ID TIMESTAMP STATUS 1 2017-02-27-11.55.48.826810 0 2 2017-02-27-11.55.55.550074 0 3 2017-02-27-11.56.04.446368 3 4 2017-02-27-11.56.08.633084 3 5 2017-02-27-11.56.12.898442 3 6 2017-02-27-11.56.18.741698 0 7 2017-02-27-11.56.24.083265 0 8 2017-02-27-11.56.28.554100 0 9 2017-02-27-11.56.34.870453 3 10 2017-02-27-11.56.47.262760 0``` – Torsten Steinbach Mar 02 '17 at 16:16
Above are the sample values that I used in my test table MACHINEDATA. With this data it works fine. – Torsten Steinbach Mar 06 '17 at 08:57

Query Tuning in Dashdb

1 Answers1