Google BigQuery

```sql CREATE TEMP FUNCTION rc( v FLOAT64, reverse BOOL) RETURNS FLOAT64 LANGUAGE js AS """ if ( reverse ) { return 5-v; } else { return v; } """; select p.HID, p.PYGENDERw2, avg(rc(cast(p.PPSY5A01w2 as FLOAT64),true ) + rc(cast(p.PPSY5A03w2 as FLOAT64),true ) + rc(cast(p.PPSY5A05w2 as FLOAT64),true ) + rc(cast(p.PPSY5A06w2 as FLOAT64),true )) / 4 as PM_1, avg(rc(cast(p.PPSY5A02w2 as FLOAT64),false) + rc(cast(p.PPSY5A04w2 as FLOAT64),false) + rc(cast(p.PPSY5A07w2 as FLOAT64),false) + rc(cast(p.PPSY5A08w2 as FLOAT64),false)) / 4 as PM_2, avg(rc(cast(y.YPSY7A01w2 as FLOAT64),true ) + rc(cast(y.YPSY7A03w2 as FLOAT64),true ) + rc(cast(y.YPSY7A05w2 as FLOAT64),true ) + rc(cast(y.YPSY7A06w2 as FLOAT64),true )) / 4 as YM_1, avg(rc(cast(y.YPSY7A02w2 as FLOAT64),false) + rc(cast(y.YPSY7A04w2 as FLOAT64),false) + rc(cast(y.YPSY7A07w2 as FLOAT64),false) + rc(cast(y.YPSY7A08w2 as FLOAT64),false)) / 4 as YM_2, avg(rc(cast(y.YFAM2A01w2 as FLOAT64),false) + rc(cast(y.YFAM2A02w2 as FLOAT64),false) + rc(cast(y.YFAM2A03w2 as FLOAT64),false) + rc(cast(y.YFAM2A04w2 as FLOAT64),false)) / 4 as YM_A, avg(rc(cast(y.YFAM2B01w2 as FLOAT64),true ) + rc(cast(y.YFAM2B02w2 as FLOAT64),true ) + rc(cast(y.YFAM2B03w2 as FLOAT64),true ) + rc(cast(y.YFAM2B04w2 as FLOAT64),true )) / 4 as YM_B, avg(rc(cast(y.YFAM2C01w2 as FLOAT64),false) + rc(cast(y.YFAM2C02w2 as FLOAT64),false) + rc(cast(y.YFAM2C03w2 as FLOAT64),false) + rc(cast(y.YFAM2C04w2 as FLOAT64),false)) / 4 as YM_C, avg(rc(cast(y.YFAM2D01w2 as FLOAT64),true ) + rc(cast(y.YFAM2D02w2 as FLOAT64),true ) + rc(cast(y.YFAM2D03w2 as FLOAT64),true ) + rc(cast(y.YFAM2D04w2 as FLOAT64),true )) / 4 as YM_D, avg(rc(cast(y.YFAM2E01w2 as FLOAT64),false) + rc(cast(y.YFAM2E02w2 as FLOAT64),false) + rc(cast(y.YFAM2E03w2 as FLOAT64),false) + rc(cast(y.YFAM2E04w2 as FLOAT64),false)) / 4 as YM_E, avg(rc(cast(y.YFAM2F01w2 as FLOAT64),true ) + rc(cast(y.YFAM2F02w2 as FLOAT64),true ) + rc(cast(y.YFAM2F03w2 as FLOAT64),true ) + rc(cast(y.YFAM2F04w2 as FLOAT64),true )) / 4 as YM_F from `eunju-308304.e01.p` as p left join `eunju-308304.e01.y` as y on p.HID = y.HID and y.PID in(1) where p.PID in(1) and p.PARENTw2 in('1','2') and p.PYGENDERw2 = '2' and y.SURVEY1w2 in (1) and y.SURVEY2w2 in (1) group by p.HID, p.PYGENDERw2 order by p.HID asc ; ```