[PUBG] 전처리 - 유저의 파밍속도 구하기

유저의 파밍속도

이전에 Python으로 전처리했던 유저별 파밍속도를 SQL을 이용하여 전처리하려면 어떻게 풀어야할지 쿼리를 작성해보았습니다.😀

table

여러개의 matchId, character_name 별로 유저가 pickupitem한 로그

문제

유저의 파밍속도를 계산하기 위한 조건은 다음과같습니다.

• 각 유저별로 아이템을 첫 파밍하는 순간부터 3분 까지만 계산
• 아이템간의 거리가 50cm 미만인 경우에만

1. PYTHON

# 유저별로 그룹화
grouped = pickup_df.groupby(['matchId','character.name'])

# 각 유저별 첫파밍을 시작으로 3분 동안 파밍한 로그를 구하고 파밍시간을 구합니다.
# 파밍한 아이템간의 좌표를 통해 0.5m 미만에 있는 경우 뭉쳐있는 아이템으로 파악 -> 이 기준으로 파밍시간을 체크

def farming_speed(group):
    group = group.sort_values('_D')  # 타임스탬프 기준으로 정렬
    end_D = group['_D'].min() + pd.Timedelta(minutes=3) # 각 경기,유저별 첫파밍 시간부터 3분이 지난 시간 계산

    group['time_diff'] = group['_D'].diff().dt.total_seconds()  # 시간 차이 계산

    # 거리 차이 계산
    group['x_diff'] = group['character.location.x'].diff() #.fillna(0)
    group['y_diff'] = group['character.location.y'].diff() #.fillna(0)
    group['z_diff'] = group['character.location.z'].diff() #.fillna(0)
    group['distance_diff'] = np.sqrt(group['x_diff']**2 + group['y_diff']**2 + group['z_diff']**2) / 100
    group = group[group['_D'] < end_D] # 첫파밍이후 3분 이내의 데이터만
    return group

# 각 그룹에 대해 함수 적용하여 필터링
df_with_check = grouped.apply(farming_speed).reset_index(drop=True)[['matchId','_D','_T','character.name','item.itemId','item.stackCount','item.category','parentItem.attachedItems','childItem.attachedItems',
                                                                     'character.location.x','character.location.y','character.location.z','time_diff','distance_diff']]

2. BigQuery

POINT

• LAG() OVER(PARTITION BY matchId, character_name ORDER BY _D)
  이전행과의 차이를 구해야함 (아이템간의 거리, 줍는속도) → matchId, character_name 별로 구해야하기 때문에 PARTITON 필요

-- Farming Speed table (User Itempickup speed)

# 각 경기, 유저별로 아이템을 주었을때 생기는 _D(TIMESTAMP) 와 x,y,z 위치를 LAG 하여 한테이블로 완성 
# Python에서 matchid, character.name 별로 groupby 하여 이전행과의 차이를 통해서 구할 수 있지만 SQL에서는 윈도우함수, 파티션을 이용하여 계산

WITH tbl as(
                SELECT  matchId, character_name, _D, item_itemId, character_location_x, character_location_y, character_location_z
                      , LAG(_D,1) OVER(PARTITION BY matchId,character_name ORDER BY _D) AS LAG_D
                      , LAG(character_location_x,1) OVER(PARTITION BY matchId,character_name ORDER BY _D) AS LAG_X
                      , LAG(character_location_y,1) OVER(PARTITION BY matchId,character_name ORDER BY _D) AS LAG_Y
                      , LAG(character_location_z,1) OVER(PARTITION BY matchId,character_name ORDER BY _D) AS LAG_Z
                      , timestamp_add(MIN(_D) OVER(PARTITION BY matchId,character_name), INTERVAL 180 second) AS LAST_D # 경기, 유저별 첫파밍 이후 3분이 지난 시간
                FROM PUBG.farming
                WHERE _T in('LogItemPickup')
          )

SELECT character_name
    , AVG(speed) as farming_speed # 집계
FROM(
      SELECT matchId
            , character_name
            , _D - LAG_D AS speed # 파밍속도계산
      FROM tbl
      WHERE SQRT(POW((LAG_X - character_location_x),2) 
                + POW((LAG_Y - character_location_y),2) 
                + POW((LAG_Z - character_location_z),2)) / 100 < 0.5  # 아이템 거리차이 조건
        AND _D < LAST_D # 첫 파밍이후 3분 조건
      ) a
GROUP BY character_name