UwHENdpv

1CREATE TABLE IF NOT EXISTS
2  `mvideo-1539598073758.bytedance.behavior_data` ( 
3    id string,
4    goods_id array <string>,
5    bhv_type string,
6    bhv_time int64,
7    user_pseudo_id string,
8    trace_id INT64,
9    device_model string,
10    platform string,
11    os string,
12    os_version string,
13    client_version string,
14    country string,
15    province string,
16    city string,
17    extra_info STRUCT < screen_type string >,
18    spm array <string>
19    );
20INSERT INTO
21  `mvideo-1539598073758.bytedance.behavior_data`
22SELECT
23  user_id AS id,
24  ARRAY(SELECT item_id FROM UNNEST(items)) as goods_id,
25  event_name AS bhv_type,
26  event_timestamp AS bhv_time,
27  user_pseudo_id,
28  value.int_value as trace_id,
29  device.mobile_model_name AS device_model,
30  platform,
31  device.operating_system AS os,
32  device.operating_system_version AS os_version,
33  app_info.version AS client_version,
34  geo.country AS country,
35  geo.region AS province,
36  geo.city AS city,
37  (SELECT AS STRUCT 
38  (SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'screen_type' ) AS screen_type) as extra_info,
39  array(SELECT concat('APP$##$Homepage$##$', case 
40    when item_list_name = 'М.видео рекомендует' then 'Selection$#M.Video recommends' 
41    when item_list_name = 'Товары дня' then 'product_of_the_day'
42    when item_list_name = 'Хиты продаж' then 'best_sellers'
43    when item_list_name = 'Новинки' then 'Selection$#new_products'
44    when item_list_name = 'В тренде' then 'Selection$#trending'
45    end,  '#$$##$$##$', item_list_index) from UNNEST(items) where item_list_index is not null and item_list_index != '(not set)'  and item_list_name in ('М.видео рекомендует', 'Товары дня', 'Хиты продаж', 'Новинки', 'В тренде') )  as spm
46FROM
47  `mvideo-1539598073758.analytics_234497047.events_*`,
48  UNNEST (event_params)
49Where event_date >= '20210628' and key = 'ga_session_id'