Navicat for mysql 账期用户分析代码

发布时间：2023-06-13 12:27:22

--账期分析代码nSELECT n bp.pid as &#34;pid&#34;,ntbp.uid as &#34;user_id&#34;,ntpers.ou as &#34;ou&#34;,ntbp.oid as &#

--账期分析代码nSELECT n bp.pid as "pid",ntbp.uid as "user_id",ntpers.ou as "ou",ntbp.oid as "order_id",n o.create_at as "order_time",nto.order_price as "payed_money",nto.type as "payed_type",nto.order_status as "order_status",n o.payed_at as "payed_time", n bp.start_at as "binlling_start_time"t,ntbp.duration as "binlling_duration",n bp.expire_at as "binlling_expire_time",ntbp.status as "bill_plan_status",ntbp.un_subscribed_at as "un_subscribed_time",ntoi.pid as "product_id",ntpod.name as "product_name",ntbp.created_at as "create_time"ntn INTO A_20180801_billing_order_productnFROM n "billing_plan" bp ,n "order" o,nt "order_item" oi,nt "product" pod,nt "person" persnwhere ntbp.start_at >= '2018-08-01'n and bp.oid = o.oidnt and o.id = oi.order_id nt and pod.pid = oi.pid nt and o.uid = pers.uid ;nt --添加主键nt alter table A_20180801_billing_order_product add PRIMARY KEY( pid);nt nt nt select apm.id, apm.name ,apm.pid ,apm.product_status, pf."value" as duration into all_month_product from product apm,tproduct_feature pf, product_features pfs n where apm.id = pfs.product_id ANDn pf.id = pfs.features_idtANDn pf.measurement = 'Duration'ANDn pf.measure_unit = 'MONTHS';nttttntselect n bop.*,n amp.duration as "product_duration"n n INTO "a_20180801_product_billing_order_duration"nnfrom all_month_product amp,a_20180801_billing_order_product bop where amp.pid = bop.product_id and bop.payed_money <500 and bop.payed_type !='OFFLINEPAY';nn--有效套餐数ncreate OR REPLACE FUNCTION active_product_users_month(at_month varchar,at_school varchar,at_year varchar default '2018-')nRETURNS integer as $total$nDECLAREn nt total integer;nttt time_1 varchar;ntt time_2 varchar;nBEGIN n time_1 := $3 || $1 || '-01' ;n select date(time_1) + interval '1 month' into time_2;nttnt select count(DISTINCT(user_id)) into total from a_20180801_product_billing_order_duration nttwhere ou = $2 and nttbinlling_start_time < date(time_2) and nttbinlling_expire_time >= date(time_1);nt nttRETURN total;nEND;n$total$ LANGUAGE plpgsql;nnselect active_product_users_month('08','12712');nselect active_product_users_month('09','12712');nselect active_product_users_month('10','12712');nselect active_product_users_month('11','12712');nselect active_product_users_month('12','12712');nnnn--新增包学期人数ncreate OR REPLACE FUNCTION new_term_users_month(at_month varchar,at_school varchar,at_year varchar default '2018-')nRETURNS integer as $total$nDECLAREn nt total integer;nttt time_1 varchar;ntt time_2 varchar;nttt time_3 varchar;nBEGIN n time_1 := $3 || $1 || '-01 00:00:00' ;n select date(time_1) + interval '1 month' into time_2;nttselect date(time_1) + interval '-1 month' into time_3;nttnt ntselect count(DISTINCT(user_id)) into total from a_20180801_product_billing_order_duration nttwhere ou = $2 and nttbinlling_start_time < date(time_2) and nttbinlling_expire_time >= date(time_1) and nttproduct_duration >= 4 and nttuser_id in ( nttselect DISTINCT(user_id) from a_20180801_product_billing_order_duration nttttwhere ou = $2 and nttttbinlling_start_time < date(time_2) and nttttbinlling_expire_time >= date(time_1)nttttnttEXCEPTtnttttttnttselect DISTINCT(user_id) from a_20180801_product_billing_order_duration nttttwhere ou = $2 and nttttbinlling_start_time < date(time_1) and nttttbinlling_expire_time >= date(time_3)ntt);nt nttRETURN total;nEND;n$total$ LANGUAGE plpgsql;nnselect new_term_users_month('09','12712');nselect new_term_users_month('10','12712');nselect new_term_users_month('11','12712');nselect new_term_users_month('12','12712');nnn--新增非包学期ncreate OR REPLACE FUNCTION new_month_users_month(at_month varchar,at_school varchar,at_year varchar default '2018-')nRETURNS integer as $total$nDECLAREn nt total integer;nttt time_1 varchar;ntt time_2 varchar;nttt time_3 varchar;nBEGIN n time_1 := $3 || $1 || '-01 00:00:00' ;n select date(time_1) + interval '1 month' into time_2;nttselect date(time_1) + interval '-1 month' into time_3;nttnt ntselect count(DISTINCT(user_id)) into total from a_20180801_product_billing_order_duration nttwhere ou = $2 and nttbinlling_start_time < date(time_2) and nttbinlling_expire_time >= date(time_1) and nttproduct_duration < 4 and nttuser_id in ( nttselect DISTINCT(user_id) from a_20180801_product_billing_order_duration nttttwhere ou = $2 and nttttbinlling_start_time < date(time_2) and nttttbinlling_expire_time >= date(time_1)nttttnttEXCEPTtnttttttnttselect DISTINCT(user_id) from a_20180801_product_billing_order_duration nttttwhere ou = $2 and nttttbinlling_start_time < date(time_1) and nttttbinlling_expire_time >= date(time_3)ntt);nt nttRETURN total;nEND;n$total$ LANGUAGE plpgsql;nnselect new_month_users_month('09','12712');nselect new_month_users_month('10','12712');nselect new_month_users_month('11','12712');nselect new_month_users_month('12','12712');nnnn--新增用户数ncreate OR REPLACE FUNCTION new_users_month(at_month varchar,at_school varchar,at_year varchar default '2018-')nRETURNS integer as $total$nDECLAREn nt total integer;nttt time_1 varchar;ntt time_2 varchar;nttt time_3 varchar;nBEGIN n time_1 := $3 || $1 || '-01 00:00:00' ;n select date(time_1) + interval '1 month' into time_2;nttselect date(time_1) + interval '-1 month' into time_3;nttnt ntselect count(1) into total from ( nttselect DISTINCT(user_id) from a_20180801_product_billing_order_duration nttttwhere ou = $2 and nttttbinlling_start_time < date(time_2) and nttttbinlling_expire_time >= date(time_1)nttttnttEXCEPTtnttttttnttselect DISTINCT(user_id) from a_20180801_product_billing_order_duration nttttwhere ou = $2 and nttttbinlling_start_time < date(time_1) and nttttbinlling_expire_time >= date(time_3)ntt) _a;nt nttRETURN total;nEND;n$total$ LANGUAGE plpgsql;nnselect new_users_month('09','12712');nselect new_users_month('10','12712');nselect new_users_month('11','12712');nselect new_users_month('12','12712');nnnn--用户流失nncreate OR REPLACE FUNCTION lost_users_month(at_month varchar,at_school varchar,at_year varchar default '2018-')nRETURNS integer as $total$nDECLAREn nt total integer;nttt time_1 varchar;ntt time_2 varchar;nttt time_3 varchar;nBEGIN n time_1 := $3 || $1 || '-01 00:00:00' ;n select date(time_1) + interval '1 month' into time_2;nttselect date(time_1) + interval '-1 month' into time_3;nttnt ntselect count(1) into total from ( nttselect DISTINCT(user_id) from a_20180801_product_billing_order_duration nttttwhere ou = $2 and nttttbinlling_start_time < date(time_1) and nttttbinlling_expire_time >= date(time_3)nttttnttEXCEPTtnttttttnttselect DISTINCT(user_id) from a_20180801_product_billing_order_duration nttttwhere ou = $2 and nttttbinlling_start_time < date(time_2) and nttttbinlling_expire_time >= date(time_1)ntt) _a;nt nttRETURN total;nEND;n$total$ LANGUAGE plpgsql;nnselect lost_users_month('09','12712');nselect lost_users_month('10','12712');nselect lost_users_month('11','12712');nselect lost_users_month('12','12712');nnnnn--到期用户数ncreate OR REPLACE FUNCTION expire_users_month(at_month varchar,at_school varchar,at_year varchar default '2018-')nRETURNS integer as $total$nDECLAREn nt total integer;nttt time_1 varchar;ntt time_2 varchar;ntttnttt nBEGIN n time_1 := $3 || $1 || '-01 00:00:00' ;n select date(time_1) + interval '1 month' into time_2;nttntt nttnttselect count(DISTINCT(user_id)) into total from a_20180801_product_billing_order_duration nttttwhere ou = $2 and nttttbinlling_start_time < date(time_2)nttttand binlling_expire_time >= date(time_1)nttttand binlling_expire_time < date(time_2);nt nttRETURN total;nEND;n$total$ LANGUAGE plpgsql;nnselect expire_users_month('08','12712');nselect expire_users_month('09','12712');nselect expire_users_month('10','12712');nselect expire_users_month('11','12712');nselect expire_users_month('12','12712');nnnn--续购用户数ncreate OR REPLACE FUNCTION renew_users_month(at_month varchar,at_school varchar,at_year varchar default '2018-')nRETURNS integer as $total$nDECLAREn nt total integer;nttt time_1 varchar;ntt time_2 varchar;nttt time_3 varchar;nttt nBEGIN n time_1 := $3 || $1 || '-01 00:00:00' ;n select date(time_1) + interval '1 month' into time_2;nttselect date(time_1) + interval '-1 month' into time_3;ntt nttselect count(1) into total from (ntttttselect DISTINCT(user_id) from a_20180801_product_billing_order_duration ntttttttwhere ou = $2 and ntttttttbinlling_start_time < date(time_1)ntttttttand binlling_expire_time >= date(time_3)ntttttttand binlling_expire_time < date(time_1)nttttINTERSECT tttnntttttselect DISTINCT(user_id) from a_20180801_product_billing_order_duration ntttttttwhere ou = $2 and ntttttttbinlling_start_time < date(time_2) and ntttttttbinlling_expire_time >= date(time_1)ntttt) _a;nt nttRETURN total;nEND;n$total$ LANGUAGE plpgsql;nnnnselect renew_users_month('09','12712');nselect renew_users_month('10','12712');nselect renew_users_month('11','12712');nselect renew_users_month('12','12712');nnn

其中：

$表示占位符；

time1:当月

interval '1 month' ：下一个月

interval '-1 month' ：上一个月

${param}传递的参数会被当成sql语句中的一部分，比如传递表名，字段名

例子：(传入值为id)

order by ${param}

则解析成的sql为：

order by id

#{parm}传入的数据都当成一个字符串，会对自动传入的数据加一个双引号

例子：(传入值为id)