Shulou(Shulou.com)06/01 Report--

This article mainly explains the "big data development warehouse ads layer index calculation how to achieve", the article explains the content is simple and clear, easy to learn and understand, now please follow the editor's train of thought slowly in-depth, together to study and learn "big data development warehouse ads layer index calculation how to achieve" it!

Ads layer data is often the final result metric data, which is used in large screen display or real-time stream processing. Use the following two examples to practice how to write a business large screen display sql.

1. Member Analysis case 1.1 data preparation

The structure of the table is as follows, where this table is a daily membership table of the dws layer, such as a summary of daily membership information.

Use dws;drop table if exists dws.dws_member_start_day;create table dws.dws_member_start_day (`uid`string,-- device id) to distinguish between users `uid`string,-- uid`app _ v`string, `os_ type`string, `propagage`string, `channel`string, `area`string, `brand`string) COMMENT 'membership Day launch Summary' partitioned by (dt string) stored as parquet;1.2 membership indicator calculation

Definition of silent member: App was started only on the day of installation, and the installation time was 7 days ago

Definition of lost members: members who have not logged in in the last 30 days

1.2.1 how to calculate the number of silent members-get the members started only once, and then filter the installation time 7 days ago, use the sum window function SELECT count (*) FROM (SELECT device_id, sum (device_id) OVER (PARTITION BY device_id) AS sum_num, dt FROM dws.dws_member_start_day) tmpWHERE dt = date_add (CURRENT_DATE,-30) 2. Core transaction case 2.1 data preparation

Given a daily order dimension table, the table structure is as follows:

DROP TABLE IF EXISTS dwd.dwd_trade_orders;create table dwd.dwd_trade_orders (`orderId` int, `orderNo` string, `userId` bigint, `status` tinyint, `productMoney` decimal, `totalMoney` decimal, `payMethod` tinyint, `isPay` tinyint, `areaId` int, `tradeSrc` tinyint, `tradeType` int, `isRefund` tinyint, `dataFlag` tinyint, `createTime` string, `payTime` string, `modifiedTime` string, `start_ date` string, `end_ date` string) COMMENT 'order fact zipper list' partitioned by (dt string) STORED AS PARQUET

Among them, the order status-3 user rejects-2 unpaid orders-1 user cancels 0 to be shipped 1 delivery 2 user confirms receipt, order valid flag-1 deletion 1 is valid

Data preprocessing is not very convenient when dealing with detail fact zipper table. You can make an intermediate table. The table structure and processing of dws_trade_orders_day are as follows:

DROP TABLE IF EXISTS dws.dws_trade_orders_day;CREATE TABLE IF NOT EXISTS dws.dws_trade_orders_day (day_dt string COMMENT 'date: yyyy-MM-dd', day_cnt decimal commnet' orders per day', day_sum decimal COMMENT 'total orders per day') COMMENT 'daily order statistics' SELECT dt, count (*) cnt, sum (totalMoney) smFROM (SELECT DISTINCT orderid, dt, totalMoney FROM dwd.dwd_trade_orders WHERE status > = 0 AND dataFlag ='1') tmpGROUP BY dt INSERT OVERWRITE TABLE dws.dws_trade_orders_daySELECT dt, count (*) cnt, sum (totalMoney) smFROM (SELECT DISTINCT orderid, dt, totalMoney FROM dwd.dwd_trade_orders WHERE status > = 0 AND dataFlag ='1') tmpGROUP BY dt;SELECT * FROM dws.dws_trade_orders_dayWHERE day_dt BETWEEN '2020-01-01' AND '2020-12-31' 2.2 indicator 1, statistics on the number of sales orders and total orders per quarter in 2020

First create an ads indicator table: dws_trade_orders_quarter

DROP TABLE IF EXISTS dws.dws_trade_orders_quarter CREATE TABLE IF NOT EXISTS dws.dws_trade_orders_quarter (YEAR string COMMENT 'year', QUARTER string COMMENT 'quarter', cnt decimal COMMENT 'total number of orders' SUM decimal COMMENT 'Total order') COMMENT 'Quarterly order Statistics' INSERT OVERWRITE TABLE dws.dws_trade_orders_quarter WITH tmp AS (SELECT substr (day_dt, 0,4) YEAR, CASE WHEN substr (dat_dt, 6,2) = "01" OR substr (dat_dt, 6,2) = "02" OR substr (day_dt, 6,2) = "03" THEN "1" WHEN substr (dat_dt, 6,2) = "04" OR substr (dat_dt, 6) 2) = "05" OR substr (day_dt, 6, 2) = "06" THEN "2" WHEN substr (dat_dt, 6, 2) = "07" OR substr (dat_dt, 6, 2) = "08" OR substr (day_dt, 6, 2) = "09" THEN "3" WHEN substr (dat_dt, 6, 2) = "10" OR substr (dat_dt, 6, 2) = "11" OR substr (day_dt, 6 2) = "12" THEN "4" AS QUARTER day_cnt, day_sum FROM dws.dws_trade_orders_day) SELECT YEAR, QUARTER, sum (day_cnt), sum (day_sum) FROM tmpGROUP BY YEAR QUARTER 2.3 Statistics on the number of sales orders and the total amount of orders per month in 2020

First create an ads indicator table: dws_trade_orders_month

DROP TABLE IF EXISTS dws.dws_trade_orders_month CREATE TABLE IF NOT EXISTS dws.dws_trade_orders_month (yearstring COMMENT 'year', MONTH string COMMENT 'month', month_cnt decimal COMMENT 'total monthly orders' Month_sum decimal COMMENT 'monthly order Total') COMMENT 'monthly order Statistics' INSERT OVERWRITE TABLE dws.dws_trade_orders_month WITH tmp AS (SELECT substr (day_dt, 0,4) YEAR, sunstr (day_dt, 6,2) MONTH, day_cnt, day_sum FROM dws.dws_trade_orders_day) SELECT YEAR MONTH, sum (day_cnt) month_cnt, sum (day_sum) month_sumFROM tmpGROUP BY YEAR, MONTH 2.4 Statistics on the number of sales orders and total orders per week (Monday to Sunday) in 2020

Create an ads layer indicator table: dws_trade_orders_week leverages the date function weekofyear

DROP TABLE IF EXISTS dws.dws_trade_orders_week CREATE TABLE IF NOT EXISTS dws.dws_trade_orders_week (YEAR string COMMENT 'year', WEEK string COMMENT 'week of the year', week_cnt decimal COMMENT 'total number of weekly orders') Week_sum decimal COMMENT 'Weekly order Total') COMMENT 'Weekly order Statistics' INSERT OVERWRITE TABLE dws.dws_trade_orders_weekSELECT substr (day_dt, 0,4) YEAR, weekofyear (day_dt) WEEK, sum (day_cnt), sum (day_sum) FROM dws.dws_trade_orders_dayGROUP BY substr (day_dt, 0,4) YEAR Weekofyear (day_dt) WEEK 2.5 Statistics on the number and total number of orders for national statutory holidays, rest days and working days in 2020

Create date information dimension table: dim_day_info and enter holiday information data (the data are different from year to year and need to be notified by the State Council, so regular manual maintenance)

Drop table if exists dim.dim_day_info;create table if not exists dim.dim_day_info (day_dt string comment 'date', is_holidays int comment 'holiday logo: 0 is not 1 is', is_workday int comment 'weekday logo 0 is not 1 is') comment 'date information table' -- count the number of orders for 2020 holidays. Total orders SELECT nvl (sum (day_cnt), 0) nvl (sum (day_sum), 0) FROM dws.dws_trade_orders_day ALEFT JOIN dim.dim_day_info B ON A.day_dt = B.day_dtWHERE B.is_holiday = 1 -- Statistics on the number of orders for rest days in 2020. Total orders SELECT nvl (sum (day_cnt), 0) nvl (sum (day_sum), 0) FROM dws.dws_trade_orders_day ALEFT JOIN dim.dim_day_info B ON A.day_dt = B.day_dtWHERE B.is_workday = 0 -- count the number of orders in 2020 working days. Total orders SELECT nvl (sum (day_cnt), 0) nvl (sum (day_sum), 0) FROM dws.dws_trade_orders_day ALEFT JOIN dim.dim_day_info B ON A.day_dt = B.day_dtWHERE B.is_workday = 1 Thank you for your reading. The above is the content of "how to realize the calculation of ads layer indicators in the development of big data". After the study of this article, I believe you have a deeper understanding of how to realize the calculation of ads layer indicators in the development of big data, and the specific use still needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.