Mysql - SQL: compare one value with whole column if the value available anywhere in the same column. give a name to every value comes between those 2 values

Vish
January 9, 2024
114 views
0 votes
2 Answers

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

Tags: mysql sql

Answers

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

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

Please signup or login to give your own answer.

Click here to cancel reply.

Mysql – SQL: compare one value with whole column if the value available anywhere in the same column. give a name to every value comes between those 2 values

Answers