ROW_NUMBER()とFIRST_VALUE(url)区別(初回記録)
作業中に一度に複数回変更できる場合が多く、変更のたびにレコードが生成されます.では、最初のデータを取りたい場合は、関数を使って解決できます.hive関数の最初のデータ:1.FIRST_VALUEはパケット内のソートを取った後、現在の行まで、最初の値(弊害:最初のフィールドしか取れない)select distinct wo_id,first_value(created_at) over(partition by wo_id order by id asc) as change_appoint_time_fir from ods.ods_mall_swo_operate_log where dt=‘2019-07-31’ and type=‘CH_SW_APPOINT’ and wo_id=‘346617364164486041’
2.ROW_NUMBER()は適用性が強く、例えば私が初めて店に着いたのは北京で、2回目の上海で、この関数を使えばいいです.select wo_id,created_at as change_appoint_time_fir from ( select wo_id,created_at,ROW_NUMBER() over(partition by wo_id order by id ) as rn from ods.ods_mall_swo_operate_log where dt=‘2019-07-31’ and type=‘CH_SW_APPOINT’)ga where rn=1 AND wo_id=‘346622775823278114’
構文:
cookie id createtime url rn first1
cookie 1 2015-04-10 10:00:00 url1 1 url1 cookie 1 2015-04-10 10:00:02 url2 2 url1 cookie 1 2015-04-10 10:03:04 url3 3 url1
2.ROW_NUMBER()は適用性が強く、例えば私が初めて店に着いたのは北京で、2回目の上海で、この関数を使えばいいです.select wo_id,created_at as change_appoint_time_fir from ( select wo_id,created_at,ROW_NUMBER() over(partition by wo_id order by id ) as rn from ods.ods_mall_swo_operate_log where dt=‘2019-07-31’ and type=‘CH_SW_APPOINT’)ga where rn=1 AND wo_id=‘346622775823278114’
構文:
SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS first1
FROM test
cookie id createtime url rn first1
cookie 1 2015-04-10 10:00:00 url1 1 url1 cookie 1 2015-04-10 10:00:02 url2 2 url1 cookie 1 2015-04-10 10:03:04 url3 3 url1