最近完成了一个新项目用druid的json查询的方式,项目做完了在这里总结一下:
一、
【简单SQL】:
SELECT
SUM(initiate_count) AS chatCount,
SUM(online_initiate_count) AS onlineChatCount,
SUM(initiate_chat_count) AS startChatCount,
SUM(effective_chat_count) AS effectiveChatCount,
SUM(info_submit_card_count) AS keywordCard,
SUM(keyword_click_count) AS keywordClick
FROM
f_fact_landing_page
WHERE
__time TIMESTAMP BETWEEN '2018-08-13 00:00:00'
AND '2018-08-13 23:59:59'
【简单SQL对应druid的json请求体】:
{
"queryType": "timeseries",
"dataSource": "f_fact_landing_page",
"granularity": "all",
"intervals": [
"2018-08-13T00:00/2018-08-13T23:59:59"
],
"aggregations": [
{
"type": "longSum",
"fieldName": "initiate_count",
"name": "chatCount"
},
{
"type": "longSum",
"fieldName": "online_initiate_count",
"name": "onlineChatCount"
},
{
"type": "longSum",
"fieldName": "initiate_chat_count",
"name": "startChatCount"
},
{
"type": "longSum",
"fieldName": "effective_chat_count",
"name": "effectiveChatCount"
},
{
"type": "longSum",
"fieldName": "info_submit_card_count",
"name": "keywordCard"
},
{
"type": "longSum",
"fieldName": "keyword_click_count",
"name": "keywordClick"
}
]
}
二、
SQL的group by/order by/limit操作:
SELECT
SUM(keyword_click_count) AS keywordClick,
SUM(info_submit_card_count) AS keywordCard,
SUM(keyword_cash_amount) AS keywordCash,
SUM(keyword_cost_amount) AS keywordCost,
SUM(educate_amount) AS journalAccount,
COUNT(
DISTINCT load_cost_vv_id_unique
) AS visitTimes,
COUNT(
DISTINCT load_cost_uid_unique
) AS visitPeopleCount,
SUM(load_cost_count) AS visitPageCount,
SUM(split_screen_count) AS splitScreenCount,
SUM(stay_time_stay_time_amount) AS stayTime SUM(load_cost_cost_amount) AS loadCost,
SUM(click_submit_count) AS submitClickCount,
SUM(initiate_count) AS chatCount,
SUM(online_initiate_count) AS onlineChatCount,
SUM(initiate_chat_count) AS startChatCount,
SUM(effective_chat_count) AS effectiveChatCount
FROM
f_fact_landing_page
WHERE
advertiser_code IN (
"360",
"baidu",
"shenma",
"sogou"
)
AND __time TIMESTAMP BETWEEN '2018-08-13 00:00:00'
AND '2018-08-13 23:59:59'
GROUP BY
site_name,
site_id
ORDER BY
keywordClick
LIMIT 10000
【SQL的group by/order by/limit操作对应的druid的json请求体】
{
"queryType": "groupBy",
"dataSource": "f_fact_landing_page",
"granularity": "all",
"intervals": [
"2018-08-13T00:00/2018-08-13T23:59:59"
],
"dimensions": [
"site_name",
"site_id"
],
"filter": {
"type": "in",
"dimension": "advertiser_code",
"values": [
"360",
"baidu",
"shenma",
"sogou"
]
},
"aggregations": [
{
"type": "longSum",
"fieldName": "keyword_click_count",
"name": "keywordClick"
},
{
"type": "longSum",
"fieldName": "info_submit_card_count",
"name": "keywordCard"
},
{
"type": "doubleSum",
"fieldName": "keyword_cash_amount",
"name": "keywordCash"
},
{
"type": "doubleSum",
"fieldName": "keyword_cost_amount",
"name": "keywordCost"
},
{
"type": "doubleSum",
"fieldName": "educate_amount",
"name": "journalAccount"
},
{
"type": "hyperUnique",
"fieldName": "load_cost_vv_id_unique",
"name": "visitTimes"
},
{
"type": "hyperUnique",
"fieldName": "load_cost_uid_unique",
"name": "visitPeopleCount"
},
{
"type": "longSum",
"fieldName": "load_cost_count",
"name": "visitPageCount"
},
{
"type": "longSum",
"fieldName": "split_screen_count",
"name": "splitScreenCount"
},
{
"type": "doubleSum",
"fieldName": "stay_time_stay_time_amount",
"name": "stayTime"
},
{
"type": "doubleSum",
"fieldName": "load_cost_cost_amount",
"name": "loadCost"
},
{
"type": "longSum",
"fieldName": "click_submit_count",
"name": "submitClickCount"
},
{
"type": "longSum",
"fieldName": "initiate_count",
"name": "chatCount"
},
{
"type": "longSum",
"fieldName": "online_initiate_count",
"name": "onlineChatCount"
},
{
"type": "longSum",
"fieldName": "initiate_chat_count",
"name": "startChatCount"
},
{
"type": "longSum",
"fieldName": "effective_chat_count",
"name": "effectiveChatCount"
}
],
"limitSpec": {
"type": "default",
"limit": "10000",
"columns": [
{
"dimension": "keywordClick",
"direction": "desc",
"dimensionOrder": "numeric"
}
]
}
}
三、
【SQL的groupby/having】:
SELECT
SUM(keyword_cost_amount) AS keywordCost
FROM
f_fact_landing_page
WHERE
advertiser_code IN (
"360",
"baidu",
"shenma",
"sogou"
)
AND __time TIMESTAMP BETWEEN '2018-08-13 00:00:00'
AND '2018-08-13 23:59:59'
GROUP BY
promote_url_decode
HAVING
keywordCost > 0
【SQL的groupby/having对应的druid的json请求体】:
{
"queryType": "groupBy",
"dataSource": "f_fact_landing_page",
"granularity": "all",
"intervals": [
"2018-08-13T00:00/2018-08-13T23:59:59"
],
"dimensions": [
"promote_url_decode"
],
"filter": {
"type": "in",
"dimension": "advertiser_code",
"values": [
"360",
"baidu",
"shenma",
"sogou"
]
},
"aggregations": [
{
"type": "doubleSum",
"fieldName": "keyword_cost_amount",
"name": "keywordCost"
}
],
"having": {
"type": "and",
"havingSpecs": [
{
"type": "greaterThan",
"aggregation": "keywordCost",
"value": "0"
}
]
}
}
四、
【有二级指标,以一级指标相除为例】:
SELECT
SUM(keyword_click_count) AS keywordClick,
SUM(info_submit_card_count) AS keywordCard,
(SUM(keyword_click_count) / SUM(info_submit_card_count)) AS netSale,
SUM(keyword_cash_amount) AS keywordCash,
SUM(keyword_cost_amount) AS keywordCost,
SUM(educate_amount) AS journalAccount,
COUNT(DISTINCT load_cost_vv_id_unique) AS visitTimes,
COUNT( DISTINCT load_cost_uid_unique ) AS visitPeopleCount,
SUM(load_cost_count) AS visitPageCount,
SUM(split_screen_count) AS splitScreenCount,
SUM(stay_time_stay_time_amount) AS stayTime,
SUM(load_cost_cost_amount) AS loadCost,
SUM(click_submit_count) AS submitClickCount,
SUM(initiate_count) AS chatCount,
SUM(online_initiate_count) AS onlineChatCount,
SUM(initiate_chat_count) AS startChatCount,
SUM(effective_chat_count) AS effectiveChatCount
FROM
f_fact_landing_page
WHERE
advertiser_code IN ( "360", "baidu", "shenma", "sogou" )
AND __time TIMESTAMP BETWEEN '2018-08-13 00:00:00' AND '2018-08-13 23:59:59'
GROUP BY
site_name, site_id
ORDER BY
netSale ASC
LIMIT 10
【以上对应的druid的json请求体】:
{
"queryType": "groupBy",
"dataSource": "f_fact_landing_page",
"granularity": "all",
"intervals": [
"2018-08-13T00:00/2018-08-13T23:59:59"
],
"dimensions": [ "site_name", "site_id" ],
"filter": {
"type": "in",
"dimension": "advertiser_code",
"values": [ "360", "baidu", "shenma", "sogou" ]
},
"aggregations": [
{
"type": "longSum",
"fieldName": "keyword_click_count",
"name": "keywordClick"
},
{ "type":"longSum", "fieldName":"info_submit_card_count", "name":"keywordCard" },
{ "type":"doubleSum", "fieldName":"keyword_cash_amount", "name":"keywordCash" },
{ "type":"doubleSum", "fieldName":"keyword_cost_amount", "name":"keywordCost" },
{ "type":"doubleSum", "fieldName":"educate_amount", "name":"journalAccount" },
{
"type": "hyperUnique",
"fieldName": "load_cost_vv_id_unique",
"name": "visitTimes"
},
{ "type":"hyperUnique", "fieldName":"load_cost_uid_unique", "name":"visitPeopleCount" },
{ "type":"longSum", "fieldName":"load_cost_count", "name":"visitPageCount" },
{ "type":"longSum", "fieldName":"split_screen_count", "name":"splitScreenCount" },
{ "type":"doubleSum", "fieldName":"stay_time_stay_time_amount", "name":"stayTime" },
{ "type":"doubleSum", "fieldName":"load_cost_cost_amount", "name":"loadCost" },
{"type":"longSum", "fieldName":"click_submit_count" ,"name":"submitClickCount" },
{ "type":"longSum", "fieldName":"initiate_count", "name":"chatCount" },
{ "type":"longSum", "fieldName":"online_initiate_count", "name":"onlineChatCount" },
{ "type":"longSum", "fieldName":"initiate_chat_count", "name":"startChatCount" },
{ "type":"longSum", "fieldName":"effective_chat_count", "name":"effectiveChatCount" }
],
"having": {
"type": "and",
"havingSpecs": [
{
"type": "greaterThan",
"aggregation": "keywordCost",
"value": "0"
}
]
},
"postAggregations": [
{
"type": "arithmetic",
"name": "netSale",
"fn": "/",
"fields": [
{
"type": "fieldAccess",
"fieldName": "keywordCard"
},
{
"type": "fieldAccess",
"fieldName": "keywordClick"
}
]
}
],
"limitSpec": {
"type": "default",
"limit": "10",
"columns": [
{
"dimension": "netSale",
"direction": "asc",
"dimensionOrder": "numeric"
}
]
}
}
其实这些都是在druid的官网里面有的,只要细心查都能够找到,提供druid官网的Doc:http://druid.io/docs/latest/design/
如有问题,请多指教!!!