-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathfeql.script
61 lines (61 loc) · 9.4 KB
/
feql.script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
key_flattenRequest_reqId_1 = window(table=flattenRequest, output="flattenRequest_output")
flattenRequest_outputkey_flattenRequest_reqId = output(key_flattenRequest_reqId_1.reqId[0])
f_original_eventTime_1 = column(key_flattenRequest_reqId_1.eventTime[0])
f_original_pair_id_2 = column(key_flattenRequest_reqId_1.pair_id[0])
f_original_sku_id_3 = column(key_flattenRequest_reqId_1.sku_id[0])
f_original_user_id_4 = column(key_flattenRequest_reqId_1.user_id[0])
f_combine_34_27 = column(key_flattenRequest_reqId_1.sku_id[0])
f_combine_35_29 = column(key_flattenRequest_reqId_1.sku_id[0])
f_combine_36_31 = column(key_flattenRequest_reqId_1.sku_id[0])
f_combine_37_34 = column(key_flattenRequest_reqId_1.sku_id[0])
select_bo_action = select(flattenRequest, eventTime as ingestionTime,pair_id as pair_id,0L as time,'' as model_id,'' as type,'' as cate,'' as br)
bo_action_union_window = window(table=select_bo_action, other_table=[bo_action], keys=[pair_id], order=ingestionTime, at_least=10, max_size=100, offset=64d, instance_is_window=false, output="flattenRequest_output")
f_flattenRequest_bo_action_br_top3frequency_18 = column(join(",", "NULL", range(get_keys(sort_by_value(map(group_by(bo_action_union_window[0s:10h], "br"), x->count_of_window(x)))), 0, 3)))
f_flattenRequest_bo_action_cate_top3frequency_19 = column(join(",", "NULL", range(get_keys(sort_by_value(map(group_by(bo_action_union_window[0s:10h], "cate"), x->count_of_window(x)))), 0, 3)))
f_flattenRequest_bo_action_model_id_top3frequency_20 = column(join(",", "NULL", range(get_keys(sort_by_value(map(group_by(bo_action_union_window[0s:5d], "model_id"), x->count_of_window(x)))), 0, 3)))
f_flattenRequest_bo_action_type_top3frequency_21 = column(join(",", "NULL", range(get_keys(sort_by_value(map(group_by(bo_action_union_window[0s:7d], "type"), x->count_of_window(x)))), 0, 3)))
f_flattenRequest_bo_action_model_id_distinct_count_24 = column(distinct_count(bo_action_union_window.model_id[0s:14d]))
f_flattenRequest_bo_action_model_id_distinct_count_25 = column(distinct_count(bo_action_union_window.model_id[0s:7d]))
f_flattenRequest_bo_action_type_distinct_count_26 = column(distinct_count(bo_action_union_window.type[0s:14d]))
select_bo_comment = select(flattenRequest, eventTime as ingestionTime,0L as dt,sku_id as sku_id,0 as comment_num,'' as has_bad_comment,0.0 as bad_comment_rate)
bo_comment_union_window = window(table=select_bo_comment, other_table=[bo_comment], keys=[sku_id], order=ingestionTime, at_least=10, max_size=100, offset=64d, instance_is_window=false, output="flattenRequest_output")
f_flattenRequest_bo_comment_bad_comment_rate_avg_5 = column(avg(bo_comment_union_window.bad_comment_rate[0:10]))
f_flattenRequest_bo_comment_bad_comment_rate_avg_6 = column(avg(bo_comment_union_window.bad_comment_rate[0s:64d]))
f_flattenRequest_bo_comment_bad_comment_rate_min_17 = column(min(bo_comment_union_window.bad_comment_rate[0s:14d]))
f_flattenRequest_bo_comment_comment_num_top3frequency_22 = column(join(",", "NULL", range(get_keys(sort_by_value(map(group_by(bo_comment_union_window[0s:7d], "comment_num"), x->count_of_window(x)))), 0, 3)))
f_flattenRequest_bo_comment_has_bad_comment_top3frequency_23 = column(join(",", "NULL", range(get_keys(sort_by_value(map(group_by(bo_comment_union_window[0s:7d], "has_bad_comment"), x->count_of_window(x)))), 0, 3)))
f_flattenRequest_bo_comment_comment_num_distinct_count_27 = column(distinct_count(bo_comment_union_window.comment_num[0s:32d]))
f_flattenRequest_bo_comment_comment_num_distinct_count_28 = column(distinct_count(bo_comment_union_window.comment_num[0s:7d]))
f_flattenRequest_bo_comment_has_bad_comment_distinct_count_29 = column(distinct_count(bo_comment_union_window.has_bad_comment[0s:7d]))
flattenRequest_eventTime_user_id = window(table=flattenRequest, keys=[user_id], order=eventTime, max_size=200, at_least=10, offset=14d, output="flattenRequest_output")
f_flattenRequest_window_unique_count_pair_id_30 = column(distinct_count(flattenRequest_eventTime_user_id.pair_id[0:10]))
f_flattenRequest_window_top1_ratio_pair_id_31_where = where(flattenRequest_eventTime_user_id[0:10], x -> x.pair_id != null)
f_flattenRequest_window_top1_ratio_pair_id_31_group = group_by(flattenRequest_eventTime_user_id[0:10], "pair_id")
f_flattenRequest_window_top1_ratio_pair_id_31 = column(first(top(get_values(map(f_flattenRequest_window_top1_ratio_pair_id_31_group, x -> double(count(x))/count(f_flattenRequest_window_top1_ratio_pair_id_31_where))), 1)))
f_flattenRequest_window_top1_ratio_pair_id_32_where = where(flattenRequest_eventTime_user_id[0s:14d], x -> x.pair_id != null)
f_flattenRequest_window_top1_ratio_pair_id_32_group = group_by(flattenRequest_eventTime_user_id[0s:14d], "pair_id")
f_flattenRequest_window_top1_ratio_pair_id_32 = column(first(top(get_values(map(f_flattenRequest_window_top1_ratio_pair_id_32_group, x -> double(count(x))/count(f_flattenRequest_window_top1_ratio_pair_id_32_where))), 1)))
f_flattenRequest_window_unique_count_pair_id_33 = column(distinct_count(flattenRequest_eventTime_user_id.pair_id[0s:14d]))
f_flattenRequest_window_count_pair_id_43 = column(if(flattenRequest_eventTime_user_id.pair_id[0] != null, count(flattenRequest_eventTime_user_id.pair_id[0], flattenRequest_eventTime_user_id.pair_id[0s:5d]), null))
f_flattenRequest_window_count_pair_id_44 = column(if(flattenRequest_eventTime_user_id.pair_id[0] != null, count(flattenRequest_eventTime_user_id.pair_id[0], flattenRequest_eventTime_user_id.pair_id[0s:7d]), null))
join_flattenRequest = select(flattenRequest, reqId as flattenRequest_reqId, eventTime as flattenRequest_eventTime, main_id as flattenRequest_main_id, pair_id as flattenRequest_pair_id, user_id as flattenRequest_user_id, sku_id as flattenRequest_sku_id, time as flattenRequest_time, split_id as flattenRequest_split_id, time1 as flattenRequest_time1)
join_action = select(action, reqId as action_reqId, eventTime as action_eventTime, ingestionTime as action_ingestionTime, actionValue as action_actionValue)
join_bo_product = select(bo_product, ingestionTime as bo_product_ingestionTime, sku_id as bo_product_sku_id, a1 as bo_product_a1, a2 as bo_product_a2, a3 as bo_product_a3, cate as bo_product_cate, br as bo_product_br)
join_bo_user = select(bo_user, ingestionTime as bo_user_ingestionTime, user_id as bo_user_user_id, age as bo_user_age, sex as bo_user_sex, user_lv_cd as bo_user_user_lv_cd, user_reg_tm as bo_user_user_reg_tm)
join_flattenRequest_join_action_left = leftjoin(join_flattenRequest, join_action, "join_flattenRequest.flattenRequest_reqId = join_action.action_reqId")
join_flattenRequest_join_action_left_join_bo_product_left = leftjoin(join_flattenRequest_join_action_left, join_bo_product, "join_flattenRequest_join_action_left.flattenRequest_sku_id = join_bo_product.bo_product_sku_id")
join_flattenRequest_join_action_left_join_bo_product_left_join_bo_user_left = leftjoin(join_flattenRequest_join_action_left_join_bo_product_left, join_bo_user, "join_flattenRequest_join_action_left_join_bo_product_left.flattenRequest_user_id = join_bo_user.bo_user_user_id")
join_flattenRequest_join_action_left_join_bo_product_left_join_bo_user_left_window = window(join_flattenRequest_join_action_left_join_bo_product_left_join_bo_user_left, "join_flattenRequest_join_action_left_join_bo_product_left_join_bo_user_left_join_output")
f_flattenRequest_bo_product_a1_direct_7 = column(join_flattenRequest_join_action_left_join_bo_product_left_join_bo_user_left_window.bo_product_a1[0])
f_flattenRequest_bo_product_a2_direct_8 = column(join_flattenRequest_join_action_left_join_bo_product_left_join_bo_user_left_window.bo_product_a2[0])
f_flattenRequest_bo_product_a3_direct_9 = column(join_flattenRequest_join_action_left_join_bo_product_left_join_bo_user_left_window.bo_product_a3[0])
f_flattenRequest_bo_product_br_direct_10 = column(join_flattenRequest_join_action_left_join_bo_product_left_join_bo_user_left_window.bo_product_br[0])
f_flattenRequest_bo_product_cate_direct_11 = column(join_flattenRequest_join_action_left_join_bo_product_left_join_bo_user_left_window.bo_product_cate[0])
f_flattenRequest_bo_product_ingestionTime_direct_12 = column(join_flattenRequest_join_action_left_join_bo_product_left_join_bo_user_left_window.bo_product_ingestionTime[0])
f_flattenRequest_bo_user_age_direct_13 = column(join_flattenRequest_join_action_left_join_bo_product_left_join_bo_user_left_window.bo_user_age[0])
f_flattenRequest_bo_user_ingestionTime_direct_14 = column(join_flattenRequest_join_action_left_join_bo_product_left_join_bo_user_left_window.bo_user_ingestionTime[0])
f_flattenRequest_bo_user_sex_direct_15 = column(join_flattenRequest_join_action_left_join_bo_product_left_join_bo_user_left_window.bo_user_sex[0])
f_flattenRequest_bo_user_user_lv_cd_direct_16 = column(join_flattenRequest_join_action_left_join_bo_product_left_join_bo_user_left_window.bo_user_user_lv_cd[0])
f_flattenRequest_action_actionValue_direct_0 = column(join_flattenRequest_join_action_left_join_bo_product_left_join_bo_user_left_window.action_actionValue[0])
leftjoinkey_flattenRequest_reqId = output(join_flattenRequest_join_action_left_join_bo_product_left_join_bo_user_left_window.flattenRequest_reqId[0])
join_flattenRequest_join_action_left_join_bo_product_left_join_bo_user_left_join_output_flattenRequest_output = leftjoin(join_flattenRequest_join_action_left_join_bo_product_left_join_bo_user_left_join_output, flattenRequest_output, "join_flattenRequest_join_action_left_join_bo_product_left_join_bo_user_left_join_output.leftjoinkey_flattenRequest_reqId = flattenRequest_output.flattenRequest_outputkey_flattenRequest_reqId")