skip to Main Content
created_date rake_device rounded_geo_lat rounded_geo_lng idle_or_moving new_idle_moving
08-01-2024 11:27 a-b 28.29 76.39 idle idle
08-01-2024 10:27 a-b 28.29 76.39 idle idle
08-01-2024 09:27 a-b 28.29 76.39 idle idle
08-01-2024 08:27 a-b 28.29 76.39 idle idle
08-01-2024 07:27 a-b 28.29 76.39 idle idle
08-01-2024 06:27 a-b 28.29 76.39 moving idle
08-01-2024 05:27 a-b 28.28 76.39 moving idle
08-01-2024 05:21 a-b 28.29 76.39 idle idle
08-01-2024 05:12 a-b 28.29 76.39 idle idle
08-01-2024 04:12 a-b 28.29 76.39 idle idle
08-01-2024 03:12 a-b 28.29 76.39 idle idle
08-01-2024 02:12 a-b 28.24 76.39 moving moving
08-01-2024 03:13 a-b 28.24 76.39 moving moving
08-01-2024 03:09 a-b 28.245 76.33129 moving moving
08-01-2024 05:12 a-b 28.324 76.3921 idle idle
08-01-2024 05:12 a-b 28.324 76.3921 idle idle

Based on the above data, I want to derive new column new_idle_moving. The logic for this column would be: I

I have to assess every pair value of rounded_geo_lat,rounded_geo_lng. If the same pair to be found anywhere between that partition (rake_device, created_date desc) then all values coming between them should be mark as idle and moving it should be as it is.

Note: we just have to replace those moving tags as idle if previous and after lat, lng for that moving tag is the same. AND DATA IS IN THE INCREMENTAL FORMAT SO EVERY NEW HOUR NEW ENTRY WILL BE GENERATED SO WE HAVE TO COMPARE THAT TO THE ENTIRE COLUMN OF LAT LNG.

I tried the following query, but it converts all values as idle which is wrong output.

  WITH GroupedData AS (
  SELECT
    created_date,
    rake_device,
    rounded_geo_lat,
    rounded_geo_lng,
    idle_or_moving,
    MAX(idle_or_moving) OVER (PARTITION BY rake_device, grp ORDER BY created_date DESC) AS max_idle_or_moving
  FROM (
    SELECT
      created_date,
      rake_device,
      rounded_geo_lat,
      rounded_geo_lng,
      idle_or_moving,
      SUM(change_flag) OVER (PARTITION BY rake_device ORDER BY created_date DESC) AS grp
    FROM (
      SELECT
        created_date,
        rake_device,
        rounded_geo_lat,
        rounded_geo_lng,
        idle_or_moving,
        CASE WHEN LAG(rounded_geo_lat) OVER (PARTITION BY rake_device ORDER BY created_date DESC) = rounded_geo_lat
               AND LAG(rounded_geo_lng) OVER (PARTITION BY rake_device ORDER BY created_date DESC) = rounded_geo_lng
             THEN 0 ELSE 1 END AS change_flag
      FROM YourTable
    ) AS ChangeFlag
  ) AS GrpAssign
)
SELECT
  gd.created_date,
  gd.rake_device,
  gd.rounded_geo_lat,
  gd.rounded_geo_lng,
  gd.idle_or_moving,
  CASE WHEN gd.max_idle_or_moving = 'idle' THEN 'idle' ELSE gd.idle_or_moving END AS new_idle_moving
FROM GroupedData gd
ORDER BY gd.created_date DESC;

here is db fiddle link :https://dbfiddle.uk/w5pNGy5B

2

Answers


  1. It is not clear how you want to condition the NEW_IDLE_MOVING column, but you can get the data from previous/next row using LAG() and LEAD() analytic functions. There are some other columns added in the answer that could help you create the right condition for change of ‘moving’ to ‘idle’. My final result has two new columns, one with same previous and next values (_AND), and another with either previous or next same values (_OR). It is not clear what you need since you mentioned same values as previous/next row and later affecting all between those having same values. Anyway, you’ll get the picture if you try to implement the sample below:

    WITH    --  S a m p l e    D a t a :
        data_new (CREATED_DATE, RAKE_DEVICE, ROUNDED_GEO_LAT, ROUNDED_GEO_LNG, IDLE_OR_MOVING) AS
            ( Select STR_TO_DATE('2024-08-01 11:27:00', '%Y-%m-%d %H:%i:%s'), 'a-b', 28.29, 76.39, 'idle' From Dual Union All
              Select STR_TO_DATE('2024-08-01 10:27:00', '%Y-%m-%d %H:%i:%s'), 'a-b', 28.29, 76.39, 'idle' From Dual Union All
              Select STR_TO_DATE('2024-08-01 09:27:00', '%Y-%m-%d %H:%i:%s'), 'a-b', 28.29, 76.39, 'idle' From Dual Union All
              Select STR_TO_DATE('2024-08-01 08:27:00', '%Y-%m-%d %H:%i:%s'), 'a-b', 28.29, 76.39, 'idle' From Dual Union All
              Select STR_TO_DATE('2024-08-01 07:27:00', '%Y-%m-%d %H:%i:%s'), 'a-b', 28.29, 76.39, 'idle' From Dual Union All
              Select STR_TO_DATE('2024-08-01 06:27:00', '%Y-%m-%d %H:%i:%s'), 'a-b', 28.29, 76.39, 'moving' From Dual Union All
              Select STR_TO_DATE('2024-08-01 05:27:00', '%Y-%m-%d %H:%i:%s'), 'a-b', 28.28, 76.39, 'moving' From Dual Union All
              Select STR_TO_DATE('2024-08-01 05:21:00', '%Y-%m-%d %H:%i:%s'), 'a-b', 28.29, 76.39, 'idle' From Dual Union All
              Select STR_TO_DATE('2024-08-01 05:12:00', '%Y-%m-%d %H:%i:%s'), 'a-b', 28.29, 76.39, 'idle' From Dual Union All
              Select STR_TO_DATE('2024-08-01 04:12:00', '%Y-%m-%d %H:%i:%s'), 'a-b', 28.29, 76.39, 'idle' From Dual Union All
              Select STR_TO_DATE('2024-08-01 03:12:00', '%Y-%m-%d %H:%i:%s'), 'a-b', 28.29, 76.39, 'idle' From Dual Union All
              Select STR_TO_DATE('2024-08-01 02:12:00', '%Y-%m-%d %H:%i:%s'), 'a-b', 28.24, 76.39, 'moving' From Dual Union All
              Select STR_TO_DATE('2024-08-01 03:13:00', '%Y-%m-%d %H:%i:%s'), 'a-b', 28.24, 76.39, 'moving' From Dual Union All
              Select STR_TO_DATE('2024-08-01 03:09:00', '%Y-%m-%d %H:%i:%s'), 'a-b', 28.245, 76.33129, 'moving' From Dual Union All
              Select STR_TO_DATE('2024-08-01 05:12:00', '%Y-%m-%d %H:%i:%s'), 'a-b', 28.324, 76.3921, 'idle' From Dual Union All
              Select STR_TO_DATE('2024-08-01 05:12:00', '%Y-%m-%d %H:%i:%s'), 'a-b', 28.324, 76.3921, 'idle' From Dual 
            )
    
    --  M a i n    S Q L :
    SELECT    a.CREATED_DATE, a.RAKE_DEVICE, a.ROUNDED_GEO_LAT, a.ROUNDED_GEO_LNG,
              a.IDLE_OR_MOVING,
              Case When a.LAT_LNG = a.PREV_LAT_LNG And a.LAT_LNG = a.NEXT_LAT_LNG
                   Then 'idle'
              Else a.IDLE_OR_MOVING
              End "NEW_IDLE_MOVING_WITH_AND",
              Case When a.LAT_LNG = a.PREV_LAT_LNG Or a.LAT_LNG = a.NEXT_LAT_LNG
                   Then 'idle'
              Else a.IDLE_OR_MOVING
              End "NEW_IDLE_MOVING_WITH_OR",
              a.DATE_START, a.DATE_END
    FROM
      ( Select CREATED_DATE, RAKE_DEVICE, ROUNDED_GEO_LAT, ROUNDED_GEO_LNG, 
               IDLE_OR_MOVING,
               Concat(ROUNDED_GEO_LAT, ' - ', ROUNDED_GEO_LNG) "LAT_LNG",
               Concat(Coalesce(Lag(ROUNDED_GEO_LAT) Over(Order By CREATED_DATE Desc), ROUNDED_GEO_LAT), 
                      ' - ',
                      Coalesce(Lag(ROUNDED_GEO_LNG) Over(Order By CREATED_DATE Desc), ROUNDED_GEO_LNG)
               ) "PREV_LAT_LNG", 
               Concat(Coalesce(Lead(ROUNDED_GEO_LAT) Over(Order By CREATED_DATE Desc), ROUNDED_GEO_LAT), 
                      ' - ',
                      Coalesce(Lead(ROUNDED_GEO_LNG) Over(Order By CREATED_DATE Desc), ROUNDED_GEO_LNG)
               ) "NEXT_LAT_LNG",
               Coalesce(Lag(ROUNDED_GEO_LAT) Over(Order By CREATED_DATE Desc), ROUNDED_GEO_LAT) "LAT_PREV",
               Coalesce(Lead(ROUNDED_GEO_LAT) Over(Order By CREATED_DATE Desc), ROUNDED_GEO_LAT) "LAT_NEXT",
               Coalesce(Lag(ROUNDED_GEO_LNG) Over(Order By CREATED_DATE Desc), ROUNDED_GEO_LNG) "LNG_PREV",
               Coalesce(Lead(ROUNDED_GEO_LNG) Over(Order By CREATED_DATE Desc), ROUNDED_GEO_LNG) "LNG_NEXT",
               Min(CREATED_DATE) Over(Partition By Concat(ROUNDED_GEO_LAT, ' - ', ROUNDED_GEO_LNG)) "DATE_START", 
               Max(CREATED_DATE) Over(Partition By Concat(ROUNDED_GEO_LAT, ' - ', ROUNDED_GEO_LNG)) "DATE_END"
        From      data_new
        Order By  CREATED_DATE Desc, ROUNDED_GEO_LAT, ROUNDED_GEO_LNG
      ) a
    
    /*          R e s u l t :
    CREATED_DATE        RAKE_DEVICE ROUNDED_GEO_LAT ROUNDED_GEO_LNG IDLE_OR_MOVING  NEW_IDLE_MOVING_WITH_AND    NEW_IDLE_MOVING_WITH_OR DATE_START          DATE_END
    ------------------- ----------- --------------- --------------- --------------- --------------------------- ----------------------- ------------------- --------------------  
    2024-08-01 11:27:00 a-b                 28.290      76.39000    idle            idle                        idle                    2024-08-01 03:12:00 2024-08-01 11:27:00
    2024-08-01 10:27:00 a-b                 28.290      76.39000    idle            idle                        idle                    2024-08-01 03:12:00 2024-08-01 11:27:00
    2024-08-01 09:27:00 a-b                 28.290      76.39000    idle            idle                        idle                    2024-08-01 03:12:00 2024-08-01 11:27:00
    2024-08-01 08:27:00 a-b                 28.290      76.39000    idle            idle                        idle                    2024-08-01 03:12:00 2024-08-01 11:27:00
    2024-08-01 07:27:00 a-b                 28.290      76.39000    idle            idle                        idle                    2024-08-01 03:12:00 2024-08-01 11:27:00
    2024-08-01 06:27:00 a-b                 28.290      76.39000    moving          moving                      idle                    2024-08-01 03:12:00 2024-08-01 11:27:00
    2024-08-01 05:27:00 a-b                 28.280      76.39000    moving          moving                      moving                  2024-08-01 05:27:00 2024-08-01 05:27:00
    2024-08-01 05:21:00 a-b                 28.290      76.39000    idle            idle                        idle                    2024-08-01 03:12:00 2024-08-01 11:27:00
    2024-08-01 05:12:00 a-b                 28.290      76.39000    idle            idle                        idle                    2024-08-01 03:12:00 2024-08-01 11:27:00
    2024-08-01 05:12:00 a-b                 28.324      76.39210    idle            idle                        idle                    2024-08-01 05:12:00 2024-08-01 05:12:00
    2024-08-01 05:12:00 a-b                 28.324      76.39210    idle            idle                        idle                    2024-08-01 05:12:00 2024-08-01 05:12:00
    2024-08-01 04:12:00 a-b                 28.290      76.39000    idle            idle                        idle                    2024-08-01 03:12:00 2024-08-01 11:27:00
    2024-08-01 03:13:00 a-b                 28.240      76.39000    moving          moving                      moving                  2024-08-01 02:12:00 2024-08-01 03:13:00
    2024-08-01 03:12:00 a-b                 28.290      76.39000    idle            idle                        idle                    2024-08-01 03:12:00 2024-08-01 11:27:00
    2024-08-01 03:09:00 a-b                 28.245      76.33129    moving          moving                      moving                  2024-08-01 03:09:00 2024-08-01 03:09:00
    2024-08-01 02:12:00 a-b                 28.240      76.39000    moving          moving                      idle                    2024-08-01 02:12:00 2024-08-01 03:13:00                                     */
    

    … see it here https://dbfiddle.uk/NsFrZsln
    Note :
    DATE_START – DATE_END are first and last occurence of the same LAT/LNG values
    LAT_LNG/PREV_LAT_LNG/NEXT_LAT_LNG are concatenated LAT aand LNG values from current/previous/next row
    In subbquery there are (just in case) values from previous/next row for both LAT and LNG values. This way you have in same row all the values from three windowing rows

    Login or Signup to reply.
  2. See example

    select *
      ,case 
        when (select max(created_date) from data_new d2
              where d2.rake_device=d1.rake_device and d2.created_date<d1.created_date
               and d2.rounded_geo_lat=d1.rounded_geo_lat
               and d2.rounded_geo_lng=d1.rounded_geo_lng
             ) is not null
           then 'idle1'
         when rounded_geo_lat=lag(rounded_geo_lat,1,-1)
                            over(partition by rake_device order by created_date)
             and rounded_geo_lng=lag(rounded_geo_lng,1,-1)
                      over(partition by rake_device order by created_date)
           then 'idle2'
        else 'moving'
        end new_idle_moving
      ,(select max(created_date) from data_new d2
        where d2.rake_device=d1.rake_device and d2.created_date<d1.created_date
             and d2.rounded_geo_lat=d1.rounded_geo_lat
             and d2.rounded_geo_lng=d1.rounded_geo_lng
         )  prev
    from data_new d1
    

    With your test data output is

    created_date r._device r._geo_lat r._geo_lng idle_or_mo… new… prev(pos date)
    2024-08-01 02:12:00 a-b 28.240000 76.390000 moving moving null
    2024-08-01 03:09:00 a-b 28.245000 76.331290 moving moving null
    2024-08-01 03:12:00 a-b 28.290000 76.390000 idle moving null
    2024-08-01 03:13:00 a-b 28.240000 76.390000 moving idle1 2024-08-01 02:12:00
    2024-08-01 04:12:00 a-b 28.290000 76.390000 idle idle1 2024-08-01 03:12:00
    2024-08-01 05:12:00 a-b 28.290000 76.390000 idle idle1 2024-08-01 04:12:00
    2024-08-01 05:12:00 a-b 28.324000 76.392100 idle moving null
    2024-08-01 05:12:00 a-b 28.324000 76.392100 idle idle2 null
    2024-08-01 05:21:00 a-b 28.290000 76.390000 idle idle1 2024-08-01 05:12:00
    2024-08-01 05:27:00 a-b 28.280000 76.390000 moving moving null
    2024-08-01 06:27:00 a-b 28.290000 76.390000 moving idle1 2024-08-01 05:21:00
    2024-08-01 07:27:00 a-b 28.290000 76.390000 idle idle1 2024-08-01 06:27:00
    2024-08-01 08:27:00 a-b 28.290000 76.390000 idle idle1 2024-08-01 07:27:00
    2024-08-01 09:27:00 a-b 28.290000 76.390000 idle idle1 2024-08-01 08:27:00
    2024-08-01 10:27:00 a-b 28.290000 76.390000 idle idle1 2024-08-01 09:27:00
    2024-08-01 11:27:00 a-b 28.290000 76.390000 idle idle1 2024-08-01 10:27:00
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search