druid: 查询基本维度，用户新增，用户活跃，用户使用时长，用户启动次数， 用户流失留存
druid查询
post http://xxx.25.xxx.64:18082/druid/v2
查询用户新增
- 查询平台用户新增
{
"aggregations": [
{
"fieldName": "unique_user",
"type": "thetaSketch",
"name": "new_user",
"isInputThetaSketch": false,
"size": 16384
}
],
"filter": {
"fields": [
{
"type": "selector",
"dimension": "event",
"value": "register"
},
{
"type": "selector",
"dimension": "register_days",
"value": 0
},
{
"type": "selector",
"dimension": "appkey",
"value": "apprenrenwang"
},
{
"type": "selector",
"dimension": "platform",
"value": "ios"
}
],
"type": "and"
},
"intervals": "2019-10-22T16:00:00.000Z/2019-10-23T16:00:00.000Z",
"dataSource": "apprenrenwang-app_statistic",
"granularity": "all",
"postAggregations": [],
"queryType": "timeseries"
}
结果：
[
{
"timestamp": "2019-10-22T16:00:00.000Z",
"result": {
"new_user": 0.0
}
}
]
- 设备机型用户新增
{
"aggregations": [{
"fieldName": "unique_user",
"type": "thetaSketch",
"name": "new_user",
"isInputThetaSketch": false,
"size": 16384
}],
"filter": {
"fields": [{
"type": "selector",
"dimension": "event",
"value": "register"
},
{
"type": "selector",
"dimension": "register_days",
"value": 0
},
{
"type": "selector",
"dimension": "appkey",
"value": "appngaapp"
},
{
"type": "selector",
"dimension": "platform",
"value": "ios"
}
],
"type": "and"
},
"intervals": "2019-07-23T16:00:00.000Z/2019-07-24T16:00:00.000Z",
"dataSource": "appngaapp-app_statistic",
"granularity": "all",
"threshold": 100000,
"postAggregations": [],
"queryType": "topN",
"dimension": "device_type",
"metric": "new_user"
}
结果：
[
{
"timestamp": "2019-11-12T16:00:00.000Z",
"result": [
{
"new_user": 1668.0,
"app_ver": "1.0.1"
},
{
"new_user": 1499.0,
"app_ver": "1.0.3"
},
{
"new_user": 1.0,
"app_ver": "1.0"
}
]
}
]
查询用户日活
- 查询平台用户日活
{
"aggregations": [
{
"fieldName": "unique_user",
"type": "thetaSketch",
"name": "active_user",
"isInputThetaSketch": false,
"size": 16384
}
],
"filter": {
"fields": [
{
"type": "selector",
"dimension": "event",
"value": "startup"
},
{
"type": "selector",
"dimension": "appkey",
"value": "apprenrenwang"
},{
"type": "selector",
"dimension": "platform",
"value": "android"
}
],
"type": "and"
},
"intervals": "2019-12-02T16:00:00.000Z/2019-12-03T16:00:00.000Z",
"dataSource": "apprenrenwang-app_statistic",
"granularity": "all",
"threshold": 100000,
"postAggregations": [],
"queryType": "topN",
"dimension": "day",
"metric": "active_user"
}
- 查询渠道日活
{
"aggregations": [
{
"fieldName": "unique_user",
"type": "thetaSketch",
"name": "active_user",
"isInputThetaSketch": false,
"size": 16384
}
],
"filter": {
"fields": [
{
"type": "selector",
"dimension": "event",
"value": "startup"
},
{
"type": "selector",
"dimension": "appkey",
"value": "apprenrenwang"
},{
"type": "selector",
"dimension": "platform",
"value": "android"
}
],
"type": "and"
},
"intervals": "2019-12-02T16:00:00.000Z/2019-12-03T16:00:00.000Z",
"dataSource": "apprenrenwang-app_statistic",
"granularity": "all",
"postAggregations": [],
"queryType": "topN",
"threshold": 100000,
"dimension": "channel",
"metric": "active_user"
}
用户启动次数
用户平台启动次数
{
"aggregations": [
{
"fieldName": "count",
"type": "longSum",
"name": "startup_times"
}
],
"filter": {
"fields": [
{
"type": "selector",
"dimension": "event",
"value": "startup"
},
{
"type": "selector",
"dimension": "appkey",
"value": "apprenrenwang"
}
],
"type": "and"
},
"intervals": "2019-11-11T16:00:00.000Z/2019-11-12T16:00:00.000Z",
"dataSource": "apprenrenwang-app_statistic",
"granularity": "all",
"threshold": 100000,
"postAggregations": [],
"queryType": "timeseries"
}
- 用户版本启动次数
{
"aggregations": [
{
"fieldName": "count",
"type": "longSum",
"name": "startup_times"
}
],
"filter": {
"fields": [
{
"type": "selector",
"dimension": "event",
"value": "startup"
},
{
"type": "selector",
"dimension": "appkey",
"value": "apprenrenwang"
}
],
"type": "and"
},
"intervals": "2019-11-11T16:00:00.000Z/2019-11-12T16:00:00.000Z",
"dataSource": "apprenrenwang-app_statistic",
"granularity": "all",
"threshold": 100000,
"postAggregations": [],
"queryType": "topN",
"dimension": "app_ver",
"metric": "startup_times"
}
用户使用时长
- 用户平台使用时长
{
"aggregations": [
{
"fieldName": "dmn1_sum",
"type": "longSum",
"name": "access_duration"
}
],
"filter": {
"fields": [
{
"type": "selector",
"dimension": "event",
"value": "shutdown"
},
{
"type": "selector",
"dimension": "appkey",
"value": "apprenrenwang"
},
{
"type": "selector",
"dimension": "platform",
"value": "ios"
}
],
"type": "and"
},
"intervals": "2019-11-11T16:00:00.000Z/2019-11-12T16:00:00.000Z",
"dataSource": "apprenrenwang-app_statistic",
"granularity": "all",
"postAggregations": [],
"queryType": "timeseries"
}
- 用户版本使用时长
{
"aggregations": [
{
"fieldName": "dmn1_sum",
"type": "longSum",
"name": "access_duration"
}
],
"filter": {
"fields": [
{
"type": "selector",
"dimension": "event",
"value": "shutdown"
},
{
"type": "selector",
"dimension": "appkey",
"value": "apprenrenwang"
},
{
"type": "selector",
"dimension": "platform",
"value": "ios"
}
],
"type": "and"
},
"intervals": "2019-11-11T16:00:00.000Z/2019-11-12T16:00:00.000Z",
"dataSource": "apprenrenwang-app_statistic",
"granularity": "all",
"postAggregations": [],
"queryType": "topN",
"threshold": 100000,
"dimension": "app_ver",
"metric": "access_duration"
}
用户留存流失
{
"aggregations": [
{
"type": "filtered",
"filter": {
"type": "and",
"fields": [
{
"type": "interval",
"dimension": "__time",
"intervals": [
"2019-10-29T16:00:00.000Z/2019-10-30T16:00:00.000Z"
]
},
{
"type": "selector",
"dimension": "event",
"value": "startup"
},
{
"type": "selector",
"dimension": "appkey",
"value": "apprenrenwang"
}
]
},
"aggregator": {
"fieldName": "unique_user",
"type": "thetaSketch",
"name": "active_user",
"isInputThetaSketch": false,
"size": 16384
}
},
{
"type": "filtered",
"filter": {
"type": "and",
"fields": [
{
"type": "interval",
"dimension": "__time",
"intervals": [
"2019-10-28T16:00:00.000Z/2019-10-29T16:00:00.000Z"
]
},
{
"type": "selector",
"dimension": "event",
"value": "startup"
},
{
"type": "selector",
"dimension": "appkey",
"value": "apprenrenwang"
}
]
},
"aggregator": {
"fieldName": "unique_user",
"type": "thetaSketch",
"name": "new_user",
"isInputThetaSketch": false,
"size": 16384
}
}
],
"intervals": "2019-09-29T16:00:00.000Z/2019-10-30T16:00:00.000Z",
"dataSource": "apprenrenwang-app_statistic",
"granularity": "all",
"postAggregations": [
{
"field": {
"fields": [
{
"fieldName": "new_user",
"type": "fieldAccess"
},
{
"fieldName": "active_user",
"type": "fieldAccess"
}
],
"type": "thetaSketchSetOp",
"func": "NOT"
},
"type": "thetaSketchEstimate",
"name": "user_lost"
},
{
"field": {
"fields": [
{
"fieldName": "active_user",
"type": "fieldAccess"
},
{
"fieldName": "new_user",
"type": "fieldAccess"
}
],
"type": "thetaSketchSetOp",
"func": "INTERSECT"
},
"type": "thetaSketchEstimate",
"name": "user_retain"
}
],
"queryType": "timeseries"
}
- 结果
[
{
"timestamp": "2019-09-29T19:00:00.000Z",
"result": {
"user_lost": 416.1114848520276,
"active_user": 27826.81864414539,
"new_user": 720.0,
"user_retain": 315.9050456427638
}
}
]
