23 友盟项目--sparkstreaming对接kafka、集成redis--从redis中查询月留存率

从redis中查询月留存率

StatRemainRatioMonth

package com.oldboy.umeng.spark.stat;



/**
 * 统计月留存率
 */
public class StatRemainRatioMonth {
    public static void main(String[] args) throws Exception {
        SparkConf conf = new SparkConf();
        conf.setAppName("statNew");
        conf.setMaster("local[4]");
        SparkSession sess = SparkSession.builder().config(conf).enableHiveSupport().getOrCreate();

        //sess.sql("select * from row_log");
        //注册函数
        ExecSQLUtil.execRegisterFuncs(sess);

        String day = "20181105" ;
        Jedis redis = new Jedis("s101" , 6379) ;
        redis.select(1) ;
        Set<String> keys = redis.keys("*") ;
        //创建Row行类型List集合
        List<Row> rows = new ArrayList<Row>() ;
        for(String key :keys){
            String[] arr = key.split("#") ;
            String value = redis.get(key) ;
            long mintime = Long.parseLong(value.split(",")[0]) ;//redis中取出最小值
            System.out.println(DateUtil.formatDay(mintime , 0 , "yyyyMMdd"));
            rows.add(RowFactory.create(arr[0], arr[1], arr[2], arr[3], arr[4], arr[5], arr[6],mintime)) ;
        }
        //创建java rdd
        JavaRDD<Row> rdd1 = new JavaSparkContext(sess.sparkContext()).parallelize(rows);
        //结构化字段
        StructField[] fields = new StructField[8];
        fields[0] = new StructField("appid", DataTypes.StringType, false, Metadata.empty());
        fields[1] = new StructField("appversion", DataTypes.StringType, false, Metadata.empty());
        fields[2] = new StructField("brand", DataTypes.StringType, false, Metadata.empty());
        fields[3] = new StructField("appplatform", DataTypes.StringType, false, Metadata.empty());
        fields[4] = new StructField("devicestyle", DataTypes.StringType, false, Metadata.empty());
        fields[5] = new StructField("ostype", DataTypes.StringType, false, Metadata.empty());
        fields[6] = new StructField("deviceid", DataTypes.StringType, false, Metadata.empty());
        fields[7] = new StructField("mintime", DataTypes.LongType, false, Metadata.empty());
        //指定schame类型
        StructType type = new StructType(fields);
        //创建Dataset  数据表
        Dataset<Row> ds1 = sess.createDataFrame(rdd1 ,type ) ;
        //数据表创建为临时表
        ds1.createOrReplaceTempView("_tt");

        //新增设备 各个维度下的   条件：最小值所在的月 = 给出查询的时间所在的月
        String sql = "select appid ,appversion,brand,appplatform,devicestyle,ostype,deviceid " +
                             "from _tt " +
                             "where formatbymonth(mintime, 0 , 'yyyyMM') = formatbymonth('"+ day+"' , 'yyyyMMdd' , 0 , 'yyyyMM')" +
                             "group by appid ,appversion,brand,appplatform,devicestyle,ostype,deviceid " +
                             "with cube" ;

        sess.sql(sql).createOrReplaceTempView("_t2");//再创建一个临时表
        System.out.println("========================");
        //有效的新增设备,注册v1视图  //设备id 和app 不能为空   其他字段如果为null，转为NULL,因为null不参与统计
        sess.sql("select ifnull(ss.appid ,'NULLL') appid  ," +
                         "ifnull(ss.appversion  ,'NULLL') appversion   ," +
                         "ifnull(ss.appplatform ,'NULLL') appplatform  ," +
                         "ifnull(ss.brand ,'NULLL') brand  ," +
                         "ifnull(ss.devicestyle ,'NULLL') devicestyle  ," +
                         "ifnull(ss.ostype,'NULLL') ostype ," +
                         "ifnull(ss.deviceid    ,'NULLL') deviceid " +
                         " from _t2 ss" +
                         " where ss.appid is not null and ss.deviceid is not null")
                .createOrReplaceTempView("v1");
        sess.sql("select * from v1").show(1000,false);
//
        String sql2 = ResourceUtil.readResourceAsString("stat_remainratio_month2.sql") ;
        sql2 = sql2.replace("${ymd}" , day) ;
//
        //执行sql语句
        ExecSQLUtil.execSQLString(sess , sql2);
    }
}

执行sql语句

-- 计算留存率
use big12_umeng ;

-- 查询经过一个月后的活跃用户-
CREATE OR  replace TEMPORARY view v2 as
SELECT
  ifnull(s.appid       ,'NULLL') appid        ,
  ifnull(s.appversion  ,'NULLL') appversion   ,
  ifnull(s.appplatform ,'NULLL') appplatform  ,
  ifnull(s.brand       ,'NULLL') brand        ,
  ifnull(s.devicestyle ,'NULLL') devicestyle  ,
  ifnull(s.ostype      ,'NULLL') ostype       ,
  ifnull(s.deviceid    ,'NULLL') deviceid
FROM
(
  SELECT
    appid       ,
    appversion  ,
    appplatform ,
    brand       ,
    devicestyle ,
    ostype      ,
    deviceid
  FROM
    appstartuplogs
  WHERE
    ym = formatbymonth('${ymd}' , 'yyyyMMdd' , 1 , 'yyyyMM')
  group by
    appid       ,
    appversion  ,
    appplatform ,
    brand       ,
    devicestyle ,
    ostype      ,
    deviceid
    with cube
)s
WHERE
  s.appid is not NULL
  and s.deviceid is not null ;

--
-- 查询交集()
CREATE  OR  replace TEMPORARY view v3 as
SELECT
  v1.appid          ,
  v1.appversion     ,
  v1.appplatform    ,
  v1.brand          ,
  v1.devicestyle    ,
  v1.ostype         ,
  count(v1.deviceid) cnt
FROM
  v1,v2
WHERE
      v1.appid       = v2.appid
  and v1.appversion  = v2.appversion
  and v1.appplatform = v2.appplatform
  and v1.brand       = v2.brand
  and v1.devicestyle = v2.devicestyle
  and v1.ostype      = v2.ostype
  and v1.deviceid    = v2.deviceid
GROUP BY
  v1.appid       ,
  v1.appversion  ,
  v1.appplatform ,
  v1.brand       ,
  v1.devicestyle ,
  v1.ostype ;

CREATE  OR  replace TEMPORARY view v4 as
SELECT
  v1.appid          ,
  v1.appversion     ,
  v1.appplatform    ,
  v1.brand          ,
  v1.devicestyle    ,
  v1.ostype         ,
  count(v1.deviceid) cnt
FROM
  v1
GROUP BY
  v1.appid       ,
  v1.appversion  ,
  v1.appplatform ,
  v1.brand       ,
  v1.devicestyle ,
  v1.ostype ;

select * from v4 ;

SELECT
  v4.appid      ,
  v4.appversion ,
  v4.appplatform,
  v4.brand      ,
  v4.devicestyle,
  v4.ostype     ,
  ifnull(v3.cnt , 0) / v4.cnt
FROM
  v4 left outer join v3
on
      v3.appid       = v4.appid
  and v3.appversion  = v4.appversion
  and v3.appplatform = v4.appplatform
  and v3.brand       = v4.brand
  and v3.devicestyle = v4.devicestyle
  and v3.ostype      = v4.ostype