Input Data:

Store   Aisle   Bay     Angle
11      33      1       0.0
11      33      2       360.0
11      33      3       90.0
11      33      4       180.0
11      33      5       270.0
11      34      1       360.0
11      34      2       90.0
11      34      3       180.0
11      34      4       270.0
12      34      1       89.83
12      34      2       179.83
12      34      3       269.83
12      34      4       269.83
12      34      5       359.83

Scenario: Need to frame this logic in either Bigquery SQL Query or using Python for identifying table direction(Order of direction - Front/Right End/Back/Left End) using Angle Column.

Conditions:

1. Stores have angle starting at 0. Need to make a logic for identifying the lowest angle and that becomes the front facing (0 angle)
2. Add 90 degree to this to get the direction of other tables.

Expected Output:

Store   Aisle   Bay     Angle       Direction
11      33      1       0.0         Front
11      33      2       360.0       Front
11      33      3       90.0        Right End
11      33      4       180.0       Back
11      33      5       270.0       Left End
11      34      1       360.0       Front
11      34      2       90.0        Right End
11      34      3       180.0       Back
11      34      4       270.0       Left End
12      34      1       89.83       Front
12      34      2       179.83      Right End
12      34      3       269.83      Back
12      34      4       269.83      Back
12      34      5       359.83      Left End

Any help will be appreciated. Thank You!

CodePudding user response：

Consider below approach

select *,
  case div(cast(angle - min(if(angle = 360, 0, angle)) over(partition by store, aisle) as int64), 90)
    when 1 then 'Right'
    when 2 then 'Back'
    when 3 then 'Left'
    else 'Front'
  end as direction
from your_table          

if applied to sample data in y our question - output is