基于BigQuery SQL或Python实现通过角度列识别货架方向的技术需求

Let's solve this problem with both BigQuery SQL and Python (Pandas) approaches, following your specified rules.

BigQuery SQL Implementation #

First, let's break down the logic:

• Normalize angles by taking MOD(Angle, 360) to treat 360.0 as 0.0
• For each store, find the minimum normalized angle (this is our Front baseline)
• Calculate the offset of each angle from the store's baseline, then map the offset to the corresponding direction based on 90-degree increments

WITH normalized_data AS (
  SELECT
    Store,
    Aisle,
    Bay,
    Angle,
    -- Normalize angle: 360.0 becomes 0.0
    MOD(Angle, 360) AS normalized_angle
  FROM
    `your-project.your-dataset.your-table` -- Replace with your actual table path
),
store_min_angles AS (
  SELECT
    Store,
    MIN(normalized_angle) AS min_angle
  FROM
    normalized_data
  GROUP BY
    Store
),
direction_calculation AS (
  SELECT
    nd.Store,
    nd.Aisle,
    nd.Bay,
    nd.Angle,
    -- Calculate offset from store's baseline, wrap around 360
    MOD(nd.normalized_angle - sma.min_angle, 360) AS angle_offset
  FROM
    normalized_data nd
  JOIN
    store_min_angles sma
    ON nd.Store = sma.Store
)
SELECT
  Store,
  Aisle,
  Bay,
  Angle,
  CASE
    -- Front: offset within ±45 degrees of 0 (0 to 45, or 315 to 360)
    WHEN angle_offset BETWEEN 0 AND 45 OR angle_offset BETWEEN 315 AND 360 THEN 'Front'
    -- Right End: offset within ±45 degrees of 90
    WHEN angle_offset BETWEEN 45 AND 135 THEN 'Right End'
    -- Back: offset within ±45 degrees of 180
    WHEN angle_offset BETWEEN 135 AND 225 THEN 'Back'
    -- Left End: offset within ±45 degrees of 270
    WHEN angle_offset BETWEEN 225 AND 315 THEN 'Left End'
  END AS Direction
FROM
  direction_calculation
ORDER BY
  Store, Aisle, Bay;

Why this works: #

• For Store 11, the min normalized angle is 0.0. Offsets map directly to 0→Front, 90→Right End, 180→Back, 270→Left End (360.0 normalizes to 0, so it also gets Front)
• For Store 12, the min angle is 89.83. The offset for 179.83 is (179.83 - 89.83) = 90 → Right End; 269.83 is 180 → Back; 359.83 is (359.83 - 89.83) = 270 → Left End, which matches your expected output.

Python (Pandas) Implementation #

If you prefer using Python, here's a pandas-based solution with the same logic:

import pandas as pd

# Sample input data (replace with your actual data loading, e.g., pd.read_csv())
data = pd.DataFrame([
    [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]
], columns=['Store', 'Aisle', 'Bay', 'Angle'])

# Step 1: Normalize angles
data['normalized_angle'] = data['Angle'] % 360

# Step 2: Get min angle per store
store_min_angles = data.groupby('Store')['normalized_angle'].min().reset_index()
store_min_angles.rename(columns={'normalized_angle': 'min_angle'}, inplace=True)

# Step 3: Merge min angle back to original data and calculate offset
data = data.merge(store_min_angles, on='Store')
data['angle_offset'] = (data['normalized_angle'] - data['min_angle']) % 360

# Step 4: Map offset to direction
def get_direction(offset):
    if 0 <= offset <= 45 or 315 <= offset <= 360:
        return 'Front'
    elif 45 < offset <= 135:
        return 'Right End'
    elif 135 < offset <= 225:
        return 'Back'
    elif 225 < offset < 315:
        return 'Left End'

data['Direction'] = data['angle_offset'].apply(get_direction)

# Keep only the columns we need and sort
result = data[['Store', 'Aisle', 'Bay', 'Angle', 'Direction']].sort_values(by=['Store', 'Aisle', 'Bay'])

print(result.to_string(index=False))

Output of the Python code: #

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

This exactly matches your expected output!

内容的提问来源于stack exchange，提问作者user12345