1.3.3.7 自助SQL

优质

小牛编辑

133浏览

2023-12-01

本文主要介绍分析模块的自助SQL功能，包括自助SQL的使用场景，产品的数据模型，语法规则，以及相关注意点。

1.1. 使用场景

自助分析主要用来解决现有分析模型无法提供的复杂数据计算或者提取。典型例子如下：

计算不同模块的之间使用的比率：类似于数值之间的四则运算
计算产品的设备相关数值：目前HubbleData仅提供用户相关分析以及计算
其他复杂计算：用户使用时长的统计，不同页面跳出率的统计等等。

1.2. 自助SQL举例

计算不同功能使用比率:以电商场景中的订单比率举例

select cartNumber as 加购人数, orderNumber as 订单人数, 
round(orderNumber/cartNumber) as 订单比率 
 from 
 {
     select $day，dcount($userId) as cartNumber
     from event
     where $eventId = 'toCart'
     group by $day
 } t1
   left  outer join 
 {
     select $day，dcount（$userId） as orderNumber 
     from event
     where $eventId = 'order'
     group by $day
 }t2 on t1.$day = t2.$day

计算不同事件（功能）的TOP统计信息

    select eventId, count(1) as 触发次数,
  dcount($userId) as 用户数,dcount($deviceUdid) as 设备数
  from event
  group by eventId
  order by 触发次数

计算用户的访问时长

1.3. 产品数据模型

事件表(event)：跟埋点的相关的信息主要体现在事件表中

    "$userId": "5cc9531c48a0e12a7143c13bee058166e9e9d8e1",
    "$deviceUdid": "5cc9531c48a0e12a7143c13bee058166e9e9d8e1",
    "day" :“2019-01-02” //日期字段
    "$dataType": "e",
    "$eventId": "clickButton",
    "$occurTime": 1434556935000,
    "$serverTime": 1434556945000,
    "$processTime": 1434556947000,
    "$costTime": 20,
    "appKey": "MA-XXXX-437494F370B3",
    "$productId": "",
    "$sdkVersion": "1.0",
    "$sdkType": "iOS",
    "$sessionUuid": "1234-342423-232",
    "$ip": "172.0.0.1",
    "$country": "中国",
    "$region": "浙江",
    "$city": "杭州",
    "$appVersion": "",
    "$appChannel": "",
    "$devicePlatform": "iPad",
    "$deviceOs": "iOS",
    "$deviceOsVersion": "7.0",
    "$deviceModel": "iPhone6",
    "$deviceManufacturer": "Apple",
    "$deviceResolution": "640x960",
    "$userWifi": "true",
    "$screenWidth": "640",
    "$screenHeight": "960",
    "$deviceCarrier": "中国移动",
    "$networkType": "4G",
    "$localeLanguage": "en",
    "$category": "",
    "$label": "",
    "$currentUrl": "http://news.163.com/detail.html?from=baidu&p_from=1",  // 如果有参数，会携带
    "$urlPath": "/detail.html", // 不会携带参数
    "$currentDomain": "news.163.com",
    "$pageTitle": "网易",
    "$referrer": "https://www.baidu.com/", // 如果有参数，会携带
    "$referrerDomain": "www.baidu.com",
    "$browser": "chrome",
    "$browserVersion": "58.0.3029.110",
    "$activationtime": 1434556935000, // 该设备激活时间
    "$promotionalID": "2017 818大促",
    "$utmSource": "toutiao",
    "$utmMedium": "cpc",
    "$utmCampaign": "活动推广",
    "$utmContent": "818考拉大促",
    "$utmTerm": "化妆品，母婴，箱包",
    "$newUser": 1,
    "$utmMatchType": 0,
    "$firstLevelSource": "搜索",
    "$secondLevelSource": "www.google.com.hk",
    "$pageOpenScene": "App",
    "$hubbleId":"CslQ4lurTKVNsDV0AwOjAg",
    "$deviceAndroidId":"a0e12a7143c13",
    "$scene":"1001",
    "$sceneCategory":"快捷入口",
    "$isDebug":"1",//为1表示调试模式数据，其他情况均为正常线上数据
    "productId": "商品ID"   //自定义事件属性，请先筛选事件ID
    "productName":"商品名称" //自定义事件属性，请先筛选事件ID
    "shopId":"店铺ID"    //自定义事件属性，请先筛选事件ID
    "shopName":"店铺名称"  //自定义事件属性，清先筛选事件ID

1.4. 备注信息

为了区分产品的内置属性以及自定义属性，内置属性请务必添加"$"
产品内置日期选择条件，你可以通过产品的前端页面选择日期。使用上可以区分以下两种情况
1. 按照日期展示明细数据，此时你需要在select day配以 group by day
2. 按照日期展示汇总数据，此时不需要体现上述信息。
3. 你并不需要在筛选条件中选择日期
我们所有的模型字段都是字符串类型，所以在条件中都需要添加单引号'xx'