CREATE TABLE IF NOT EXISTS ads_ec_ec360_gmv_kpi_overview ( pay_ord_amt_fy DECIMAL COMMENT '财年_订单支付成功金额' ,mbr_cnt_std BIGINT COMMENT '历史截至当日_存量会员数' ,kpi_gmv_rate_fy DECIMAL COMMENT '财年_成交金额完成度' ) COMMENT '电商360KPI概览' PARTITIONED BY ( ds STRING COMMENT '业务日期, yyyymmdd' ) LIFECYCLE 365 ; INSERT OVERWRITE TABLE ads_ec_ec360_gmv_kpi_overview PARTITION(ds = '${biz_date}') SELECT pay_ord_amt_fy ,mbr_cnt_std ,round(pay_ord_amt_fy/30000000, 4) AS kpi_gmv_rate_fy FROM ( SELECT max(pay_ord_amt_fy) AS pay_ord_amt_fy ,max(mbr_cnt_std) AS mbr_cnt_std FROM ( SELECT 1 AS id ,sum(pay_ord_amt_fy) AS pay_ord_amt_fy ,null AS mbr_cnt_std FROM dws_ec_trd_cate_commodity_gmv_kpi_fy WHERE ds = max_pt('dws_ec_trd_cate_commodity_gmv_kpi_fy') UNION SELECT 1 AS id ,null AS pay_ord_amt_fy ,SUM(mbr_cnt) AS mbr_cnt_std FROM dws_ec_mbr_cnt_std WHERE ds = max_pt('dws_ec_mbr_cnt_std') AND reg_prov_id = '-9999' AND reg_gender = '-9999' AND age_tag = '-9999' AND user_active_type = '-9999' AND vip_level = - 9999 ) t1 GROUP BY id ) t2 ;什么意思
时间: 2024-02-10 15:32:37 浏览: 159
这段代码是创建了一个名为"ads_ec_ec360_gmv_kpi_overview"的表,该表的结构包括了三个字段,分别是订单支付成功金额(pay_ord_amt_fy)、历史截至当日存量会员数(mbr_cnt_std)和成交金额完成度(kpi_gmv_rate_fy),其中成交金额完成度是通过订单支付金额除以3000万得到,并保留了四位小数。
这段代码还包括了一条INSERT语句,将符合条件的数据插入到表中,并将数据分区到了业务日期(ds)这个字段下。具体来说,该INSERT语句的SELECT部分是从两张已存在的表(dws_ec_trd_cate_commodity_gmv_kpi_fy和dws_ec_mbr_cnt_std)中查询数据,将它们的结果进行了聚合,并得到了订单支付成功金额和历史截至当日存量会员数的最大值。最后,将这两个值作为参数,计算得到了成交金额完成度,并将这三个字段的值插入到了新创建的表中。
相关问题
将此代码DROP PROCEDURE IF EXISTS schema_change;$$ CREATE PROCEDURE schema_change() BEGIN CREATE TABLE IF NOT EXISTS bis_record_upload_list ( pkid int(11) NOT NULL AUTO_INCREMENT, hash_code varchar(255) DEFAULT NULL COMMENT '检验码', path varchar(255) DEFAULT NULL COMMENT '路径', PRIMARY KEY (pkid), UNIQUE KEY hash_code (hash_code) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4; IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'bis_record_upload_list' AND column_name = 'hash_code') THEN alter table bis_record_upload_list add hash_code varchar(255) DEFAULT NULL COMMENT '检验码'; END IF; END;$$ CALL schema_change();$$ DROP PROCEDURE IF EXISTS schema_change;$$修改成kingbase语句修改成kingbase语句
DROP PROCEDURE IF EXISTS schema_change;
CREATE OR REPLACE PROCEDURE schema_change() IS
BEGIN
CREATE TABLE IF NOT EXISTS bis_record_upload_list (
pkid SERIAL PRIMARY KEY,
hash_code VARCHAR(255) DEFAULT NULL COMMENT '检验码',
path VARCHAR(255) DEFAULT NULL COMMENT '路径'
);
IF NOT EXISTS (
SELECT *
FROM information_schema.columns
WHERE table_schema = current_database()
AND table_name = 'bis_record_upload_list'
AND column_name = 'hash_code'
)
THEN
ALTER TABLE bis_record_upload_list ADD COLUMN hash_code VARCHAR(255) DEFAULT NULL COMMENT '检验码';
END IF;
END;
$$
LANGUAGE plpgsql;
CALL schema_change();
DROP PROCEDURE IF EXISTS schema_change;