693 lines
15 KiB
SQL
693 lines
15 KiB
SQL
|
|
|
|
CREATE TABLE public.ods_track_log (
|
|
gmid bigint NOT NULL,
|
|
appid bigint NOT NULL,
|
|
app_id text NOT NULL,
|
|
app_ver text,
|
|
sdk_ver text,
|
|
channel text,
|
|
country text,
|
|
province text,
|
|
city text,
|
|
isp text,
|
|
ip text,
|
|
device_width integer,
|
|
device_height integer,
|
|
device_id text NOT NULL,
|
|
device_lang text,
|
|
device_model text,
|
|
device_brand text,
|
|
device_os text,
|
|
device_type text,
|
|
event_name text NOT NULL,
|
|
event_type text,
|
|
event_time bigint NOT NULL,
|
|
net_type text,
|
|
user_id text,
|
|
order_id text,
|
|
amount bigint,
|
|
platform text,
|
|
status integer,
|
|
servid text,
|
|
server_name text,
|
|
role_id text,
|
|
role_name text,
|
|
role_level text,
|
|
job_id text,
|
|
job_name text,
|
|
var1 text,
|
|
var2 text,
|
|
var3 text,
|
|
var4 text,
|
|
var5 text,
|
|
var6 text,
|
|
var7 text,
|
|
var8 text,
|
|
var9 text,
|
|
var10 text,
|
|
var11 text,
|
|
var12 text,
|
|
var13 text,
|
|
var14 text,
|
|
var15 text,
|
|
var16 text,
|
|
var17 text,
|
|
var18 text,
|
|
var19 text,
|
|
var20 text,
|
|
var21 text,
|
|
var22 text,
|
|
var23 text,
|
|
var24 text,
|
|
var25 text,
|
|
var26 text,
|
|
var27 text,
|
|
var28 text,
|
|
var29 text,
|
|
var30 text,
|
|
ds DATE NOT NULL,
|
|
prodid text,
|
|
prod_name text,
|
|
sub_servid text,
|
|
sub_server_name text
|
|
)
|
|
LOGICAL PARTITION BY LIST (ds)
|
|
WITH (
|
|
orientation = 'column',
|
|
distribution_key = 'appid',
|
|
partition_expiration_time = '370 day',
|
|
partition_require_filter = TRUE
|
|
|
|
);
|
|
|
|
|
|
|
|
COMMENT ON TABLE public.ods_track_log IS '日志明细表';
|
|
COMMENT ON COLUMN public.ods_track_log.device_brand IS 'from device_model';
|
|
COMMENT ON COLUMN public.ods_track_log.status IS '-1非支付事件/0预支付/1已支付';
|
|
|
|
|
|
|
|
|
|
|
|
CREATE TABLE public.dwd_device_profile (
|
|
ds date NOT NULL,
|
|
appid integer NOT NULL,
|
|
device_id text NOT NULL,
|
|
gd integer,
|
|
sd integer,
|
|
ld integer
|
|
,PRIMARY KEY (ds, appid, device_id)
|
|
)
|
|
LOGICAL PARTITION BY LIST (ds)
|
|
WITH (
|
|
orientation = 'column',
|
|
distribution_key = 'appid',
|
|
partition_expiration_time = '30 day',
|
|
partition_require_filter = TRUE
|
|
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE public.ods_rollserv_account (
|
|
gmid integer,
|
|
appid integer,
|
|
app_ver text,
|
|
sdk_ver text,
|
|
channel text,
|
|
country text,
|
|
province text,
|
|
city text,
|
|
isp text,
|
|
ip text,
|
|
device_id text,
|
|
device_model text,
|
|
device_brand text,
|
|
device_os text,
|
|
device_type text,
|
|
event_time bigint,
|
|
user_id text,
|
|
servid text,
|
|
role_id text,
|
|
job_id text,
|
|
role_level text,
|
|
role_name text,
|
|
job_name text,
|
|
ds date NOT NULL
|
|
)
|
|
LOGICAL PARTITION BY LIST (ds)
|
|
WITH (
|
|
orientation = 'column',
|
|
distribution_key = 'appid',
|
|
partition_expiration_time = '30 day',
|
|
partition_require_filter = TRUE
|
|
|
|
);
|
|
|
|
|
|
CREATE TABLE public.ods_track_log4dur (
|
|
gmid bigint NOT NULL,
|
|
appid bigint NOT NULL,
|
|
app_id text NOT NULL,
|
|
app_ver text,
|
|
sdk_ver text,
|
|
channel text,
|
|
country text,
|
|
province text,
|
|
city text,
|
|
isp text,
|
|
ip text,
|
|
device_width integer,
|
|
device_height integer,
|
|
device_id text NOT NULL,
|
|
device_lang text,
|
|
device_model text,
|
|
device_brand text,
|
|
device_os text,
|
|
device_type text,
|
|
event_name text NOT NULL,
|
|
event_type text,
|
|
event_time bigint NOT NULL,
|
|
net_type text,
|
|
user_id text,
|
|
order_id text,
|
|
amount bigint,
|
|
platform text,
|
|
status integer,
|
|
servid text,
|
|
server_name text,
|
|
role_id text,
|
|
role_name text,
|
|
role_level text,
|
|
job_id text,
|
|
job_name text,
|
|
var1 text,
|
|
var2 text,
|
|
var3 text,
|
|
var4 text,
|
|
var5 text,
|
|
var6 text,
|
|
var7 text,
|
|
var8 text,
|
|
var9 text,
|
|
var10 text,
|
|
var11 text,
|
|
var12 text,
|
|
var13 text,
|
|
var14 text,
|
|
var15 text,
|
|
var16 text,
|
|
var17 text,
|
|
var18 text,
|
|
var19 text,
|
|
var20 text,
|
|
var21 text,
|
|
var22 text,
|
|
var23 text,
|
|
var24 text,
|
|
var25 text,
|
|
var26 text,
|
|
var27 text,
|
|
var28 text,
|
|
var29 text,
|
|
var30 text,
|
|
ds date NOT NULL,
|
|
prodid text,
|
|
prod_name text,
|
|
sub_servid text,
|
|
sub_server_name text
|
|
)
|
|
LOGICAL PARTITION BY LIST (ds)
|
|
WITH (
|
|
orientation = 'column',
|
|
distribution_key = 'appid',
|
|
partition_expiration_time = '7 day',
|
|
partition_require_filter = TRUE
|
|
|
|
);
|
|
|
|
|
|
CREATE TABLE public.dwd_newly_account_payment (
|
|
appid integer,
|
|
channel text,
|
|
user_id text,
|
|
servid text,
|
|
payment_order integer,
|
|
payment_amount bigint,
|
|
ds text ,PRIMARY KEY (appid, channel,user_id,servid,ds)
|
|
)with (
|
|
orientation = 'column',
|
|
storage_format = 'orc',
|
|
bitmap_columns = 'appid',
|
|
distribution_key = 'appid',
|
|
time_to_live_in_seconds = '16416000'
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE datasci.dws_role_churn_map_task_d (
|
|
gmid text NOT NULL,
|
|
appid integer NOT NULL,
|
|
servid text NOT NULL,
|
|
server_name text,
|
|
user_id text NOT NULL,
|
|
role_id text NOT NULL,
|
|
ds date NOT NULL,
|
|
role_level text,
|
|
map text,
|
|
task text,
|
|
victor_name text,
|
|
death_person_cnt integer,
|
|
death_enemy_cnt integer,
|
|
event_time timestamp with time zone NOT NULL
|
|
)
|
|
LOGICAL PARTITION BY LIST (ds)
|
|
WITH (
|
|
orientation = 'column',
|
|
distribution_key = 'appid',
|
|
partition_expiration_time = '30 day',
|
|
partition_require_filter = TRUE
|
|
|
|
);
|
|
|
|
|
|
CREATE TABLE datasci.ads_resc_product_change_analysis_h (
|
|
event_date timestamp with time zone NOT NULL,
|
|
event_hour timestamp with time zone NOT NULL,
|
|
gm_id bigint,
|
|
app_id bigint,
|
|
app_id_str text,
|
|
app_name text,
|
|
prod_id text,
|
|
svr_id text,
|
|
svr_name text,
|
|
user_id text,
|
|
role_id text,
|
|
role_name text,
|
|
role_level text,
|
|
role_att text,
|
|
consumable_grade text,
|
|
job_id text,
|
|
job_name text,
|
|
change_type text,
|
|
change_reason text,
|
|
latest_map text,
|
|
latest_task text,
|
|
change_times_sum bigint,
|
|
change_nums_sum bigint,
|
|
change_times_increased bigint,
|
|
change_times_reduced bigint,
|
|
change_times_dropped bigint,
|
|
change_nums_increased bigint,
|
|
change_nums_reduced bigint,
|
|
change_nums_dropped bigint,
|
|
ds date NOT NULL,
|
|
change_nums_increased_sum bigint,
|
|
map_rn bigint,
|
|
prod_id_category text,
|
|
open_time timestamp with time zone,
|
|
merge_time timestamp with time zone,
|
|
prod_name text,
|
|
prod_desc text,
|
|
role_create_time timestamp with time zone,
|
|
svr_desc text,
|
|
device_id text
|
|
)
|
|
LOGICAL PARTITION BY LIST (ds)
|
|
WITH (
|
|
orientation = 'column',
|
|
distribution_key = 'app_id',
|
|
partition_expiration_time = '30 day',
|
|
partition_require_filter = TRUE
|
|
|
|
);
|
|
|
|
|
|
CREATE TABLE datasci.ads_role_first_pay_analysis_d (
|
|
app_id bigint NOT NULL,
|
|
app_id_str text,
|
|
app_name text,
|
|
user_id text,
|
|
server_id text,
|
|
server_name text,
|
|
role_id text NOT NULL,
|
|
role_name text,
|
|
job_id text,
|
|
job_name text,
|
|
create_time bigint,
|
|
first_login_time bigint,
|
|
first_pay_time bigint,
|
|
first_pay_role_level text,
|
|
order_id text,
|
|
order_amount bigint,
|
|
prod_id text,
|
|
prod_name text,
|
|
real_interval bigint,
|
|
game_interval bigint,
|
|
ds date NOT NULL,
|
|
last_game_interval integer,
|
|
last_role_level text
|
|
,PRIMARY KEY (app_id, role_id, ds)
|
|
)
|
|
LOGICAL PARTITION BY LIST (ds)
|
|
WITH (
|
|
orientation = 'column',
|
|
distribution_key = 'app_id',
|
|
partition_expiration_time = '3666 day',
|
|
partition_require_filter = TRUE
|
|
|
|
);
|
|
|
|
CREATE TABLE datasci.dws_resc_monetary_stock_detail_d (
|
|
stock_date timestamp with time zone NOT NULL,
|
|
gm_id bigint,
|
|
app_id bigint,
|
|
app_id_str text,
|
|
app_name text,
|
|
svr_id text,
|
|
svr_name text,
|
|
open_time timestamp with time zone,
|
|
merge_time timestamp with time zone,
|
|
merge_number integer,
|
|
is_primary integer,
|
|
user_id text,
|
|
role_id text,
|
|
role_name text,
|
|
prod_id text,
|
|
event_date timestamp with time zone NOT NULL,
|
|
event_hour timestamp with time zone,
|
|
event_time timestamp with time zone,
|
|
change_type text,
|
|
latest_num_day bigint,
|
|
ds date NOT NULL,
|
|
prod_name text,
|
|
prod_desc text,
|
|
role_create_time timestamp with time zone,
|
|
svr_desc text
|
|
)
|
|
LOGICAL PARTITION BY LIST (ds)
|
|
WITH (
|
|
orientation = 'column',
|
|
distribution_key = 'app_id',
|
|
partition_expiration_time = '181 day',
|
|
partition_require_filter = TRUE
|
|
|
|
);
|
|
|
|
|
|
CREATE TABLE datasci.dws_resc_product_change_detail_h (
|
|
event_date timestamp with time zone,
|
|
event_hour timestamp with time zone,
|
|
gm_id bigint,
|
|
app_id bigint,
|
|
app_id_str text,
|
|
app_name text,
|
|
prod_id text,
|
|
svr_id text,
|
|
svr_name text,
|
|
user_id text,
|
|
role_id text,
|
|
role_name text,
|
|
role_level text,
|
|
role_att text,
|
|
consumable_grade text,
|
|
job_id text,
|
|
job_name text,
|
|
change_type text,
|
|
change_reason text,
|
|
latest_map text,
|
|
latest_task text,
|
|
change_times bigint,
|
|
change_nums bigint,
|
|
event_time timestamp with time zone NOT NULL,
|
|
ds date NOT NULL,
|
|
device_id text
|
|
)
|
|
LOGICAL PARTITION BY LIST (ds)
|
|
WITH (
|
|
orientation = 'column',
|
|
distribution_key = 'app_id',
|
|
partition_expiration_time = '181 day',
|
|
partition_require_filter = TRUE
|
|
|
|
);
|
|
|
|
|
|
CREATE TABLE datasci.dws_account_game_tag_duration_d (
|
|
user_id text NOT NULL,
|
|
game_type text NOT NULL,
|
|
tag_type text NOT NULL,
|
|
game_tag text NOT NULL,
|
|
ds date NOT NULL,
|
|
real_duration bigint
|
|
,PRIMARY KEY (ds, user_id, game_type, tag_type, game_tag)
|
|
)
|
|
LOGICAL PARTITION BY LIST (ds)
|
|
WITH (
|
|
orientation = 'column',
|
|
distribution_key = 'user_id',
|
|
partition_expiration_time = '190 day',
|
|
partition_require_filter = TRUE
|
|
|
|
);
|
|
|
|
|
|
CREATE TABLE datasci.ads_resc_monetary_change_analysis_h (
|
|
event_date timestamp with time zone NOT NULL,
|
|
event_hour timestamp with time zone NOT NULL,
|
|
gm_id bigint,
|
|
app_id bigint,
|
|
app_id_str text,
|
|
app_name text,
|
|
prod_id text,
|
|
svr_id text,
|
|
svr_name text,
|
|
user_id text,
|
|
role_id text,
|
|
role_name text,
|
|
role_level text,
|
|
role_att text,
|
|
consumable_grade text,
|
|
job_id text,
|
|
job_name text,
|
|
change_type text,
|
|
change_reason text,
|
|
latest_map text,
|
|
latest_task text,
|
|
latest_num_day bigint,
|
|
open_in_7_days_flag integer,
|
|
merge_in_7_days_flag integer,
|
|
open_in_14_days_flag integer,
|
|
merge_in_14_days_flag integer,
|
|
open_in_30_days_flag integer,
|
|
merge_in_30_days_flag integer,
|
|
mon_times_sum bigint,
|
|
change_num_sum bigint,
|
|
mon_times_increased bigint,
|
|
mon_times_reduced bigint,
|
|
change_num_increased bigint,
|
|
change_num_reduced bigint,
|
|
ds date NOT NULL,
|
|
open_time timestamp with time zone,
|
|
merge_time timestamp with time zone,
|
|
prod_name text,
|
|
prod_desc text,
|
|
mon_times_circul_increased bigint,
|
|
mon_times_circul_reduced bigint,
|
|
change_num_circul_increased bigint,
|
|
change_num_circul_reduced bigint,
|
|
mon_times_tax bigint,
|
|
change_num_tax bigint,
|
|
role_create_time timestamp with time zone,
|
|
svr_desc text,
|
|
device_id text
|
|
)
|
|
LOGICAL PARTITION BY LIST (ds)
|
|
WITH (
|
|
orientation = 'column',
|
|
distribution_key = 'app_id',
|
|
partition_expiration_time = '30 day',
|
|
partition_require_filter = TRUE
|
|
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE datasci.dws_resc_monetary_change_detail_h (
|
|
event_date timestamp with time zone NOT NULL,
|
|
event_hour timestamp with time zone NOT NULL,
|
|
gm_id bigint,
|
|
app_id bigint,
|
|
app_id_str text,
|
|
app_name text,
|
|
prod_id text,
|
|
svr_id text,
|
|
svr_name text,
|
|
user_id text,
|
|
role_id text,
|
|
role_name text,
|
|
role_level text,
|
|
role_att text,
|
|
consumable_grade text,
|
|
job_id text,
|
|
job_name text,
|
|
change_type text,
|
|
change_reason text,
|
|
latest_map text,
|
|
latest_task text,
|
|
mon_times bigint,
|
|
change_num bigint,
|
|
latest_num bigint,
|
|
event_time timestamp with time zone NOT NULL,
|
|
ds date NOT NULL,
|
|
get_prod_id text,
|
|
get_prod_num text,
|
|
extra_info text,
|
|
device_id text
|
|
)
|
|
LOGICAL PARTITION BY LIST (ds)
|
|
WITH (
|
|
orientation = 'column',
|
|
distribution_key = 'app_id',
|
|
partition_expiration_time = '181 day',
|
|
partition_require_filter = TRUE
|
|
|
|
);
|
|
|
|
CREATE TABLE datasci.dws_role_churn_log_original_d (
|
|
appid bigint NOT NULL,
|
|
user_id text NOT NULL,
|
|
role_id text NOT NULL,
|
|
ds text NOT NULL,
|
|
event_time bigint NOT NULL,
|
|
sub_status text,
|
|
channel text,
|
|
device_id text
|
|
)
|
|
LOGICAL PARTITION BY LIST (ds)
|
|
WITH (
|
|
orientation = 'column',
|
|
distribution_key = 'app_id',
|
|
partition_expiration_time = '31 day',
|
|
partition_require_filter = TRUE
|
|
|
|
);
|
|
|
|
|
|
CREATE TABLE datasci.dws_role_last_action_d (
|
|
app_id integer NOT NULL,
|
|
app_name text,
|
|
svr_id text,
|
|
svr_name text,
|
|
user_id text,
|
|
role_id text NOT NULL,
|
|
role_name text,
|
|
job_id text,
|
|
job_name text,
|
|
last_map text,
|
|
death_enemy_cnt integer,
|
|
death_person_cnt integer,
|
|
victor_type text,
|
|
victor_role_id text,
|
|
victor_role_name text,
|
|
victor_level text,
|
|
victor_job text,
|
|
last_task_id text,
|
|
last_task_name text,
|
|
last_task_status integer,
|
|
last_role_level text,
|
|
ds date NOT NULL
|
|
,PRIMARY KEY (app_id, role_id, ds)
|
|
)
|
|
LOGICAL PARTITION BY LIST (ds)
|
|
WITH (
|
|
orientation = 'column',
|
|
distribution_key = 'app_id',
|
|
partition_expiration_time = '31 day',
|
|
partition_require_filter = TRUE
|
|
|
|
);
|
|
|
|
|
|
CREATE TABLE public.ods_game_pm_log (
|
|
gmid bigint NOT NULL,
|
|
appid bigint NOT NULL,
|
|
app_id text NOT NULL,
|
|
app_ver text,
|
|
sdk_ver text,
|
|
channel text NOT NULL,
|
|
country text,
|
|
province text,
|
|
city text,
|
|
isp text,
|
|
ip text,
|
|
device_width integer,
|
|
device_height integer,
|
|
device_id text NOT NULL,
|
|
device_lang text,
|
|
device_model text,
|
|
device_brand text,
|
|
device_os text,
|
|
device_type text,
|
|
event_name text NOT NULL,
|
|
event_type text,
|
|
event_time timestamp with time zone NOT NULL,
|
|
net_type text,
|
|
user_id text,
|
|
order_id text,
|
|
amount bigint,
|
|
platform text,
|
|
status integer,
|
|
servid text,
|
|
server_name text,
|
|
role_id text,
|
|
role_name text,
|
|
role_level text,
|
|
job_id text,
|
|
job_name text,
|
|
var1 text,
|
|
var2 text,
|
|
var3 text,
|
|
var4 text,
|
|
var5 text,
|
|
var6 text,
|
|
var7 text,
|
|
var8 text,
|
|
var9 text,
|
|
var10 text,
|
|
var11 text,
|
|
var12 text,
|
|
var13 text,
|
|
var14 text,
|
|
var15 text,
|
|
var16 text,
|
|
var17 text,
|
|
var18 text,
|
|
var19 text,
|
|
var20 text,
|
|
var21 text,
|
|
var22 text,
|
|
var23 text,
|
|
var24 text,
|
|
var25 text,
|
|
var26 text,
|
|
var27 text,
|
|
var28 text,
|
|
var29 text,
|
|
var30 text,
|
|
ds date NOT NULL,
|
|
prodid text,
|
|
prod_name text,
|
|
sub_servid text,
|
|
sub_server_name text
|
|
)
|
|
LOGICAL PARTITION BY LIST (ds)
|
|
WITH (
|
|
orientation = 'column',
|
|
distribution_key = 'app_id',
|
|
partition_expiration_time = '16 day',
|
|
partition_require_filter = TRUE
|
|
|
|
); |