由于日志數(shù)據(jù)存在ES項(xiàng)目里，需要從ES中獲取日志進(jìn)行分析，使用SQL數(shù)據(jù)進(jìn)行處理，如下：

select

? ? ?traceid-- ? ?STRING ? COMMENT '流程id',

? ? ,appnum ?-- ? BIGINT ? COMMENT '迭代號(hào)',

? ? ,appversion --STRING ? COMMENT 'APP版本', ? ?

? ? ,appcode ? -- STRING ? COMMENT '應(yīng)用編碼',

? ? ,type ? ? -- ?STRING ? COMMENT '類型',

? ? ,spanid ? -- ?STRING ? COMMENT '模塊id', ?

? ? ,apptype ? -- STRING ? COMMENT '應(yīng)用類型詳情見定義',

? ? ,eventtime -- DATETIME COMMENT '日期',

? ? ,name ? ? ?-- STRING ? COMMENT '名稱',

? ? ,id ? ? ? ?-- STRING ? COMMENT 'id',

? ? ,theid ? ? -- STRING ? COMMENT 'theId' ?

? ? ,preid

? ? -------------data---------------- ? ?

? ? ,GET_JSON_OBJECT(data_tmp,'$.allInOne') AS allInOne ?

? ? ,GET_JSON_OBJECT(data_tmp,'$.class') ? ? ?AS class

? ? ,GET_JSON_OBJECT(data_tmp,'$.classCode') ? ? ?AS classCode

? ? ,GET_JSON_OBJECT(data_tmp,'$.deviceId') ? ? AS deviceId

? ? ,GET_JSON_OBJECT(data_tmp,'$.grade') ? AS grade

? ? ,GET_JSON_OBJECT(data_tmp,'$.gradeCode') AS gradeCode

? ? ,GET_JSON_OBJECT(data_tmp,'$.handleTime') AS handleTime

? ? ,GET_JSON_OBJECT(data_tmp,'$.heigth') AS heigth

? ? ,cast(ipint(GET_JSON_OBJECT(json_build,'$.ip')) as string) AS ipAddr

? ? ,GET_JSON_OBJECT(data_tmp,'$.isSuccess') AS isSuccess ? ? --isSuccess(1.是 0否)

? ? ,GET_JSON_OBJECT(data_tmp,'$.loginMode') AS loginMode ? ? -- 登陸模式 1 游客登陸 2 賬戶登陸

? ? ,GET_JSON_OBJECT(data_tmp,'$.loginType') AS loginType ? ? -- 登陸方式 1：在線登陸 2 離線登陸

? ? ,GET_JSON_OBJECT(data_tmp,'$.school') AS school

? ? ,GET_JSON_OBJECT(data_tmp,'$.schoolCode') AS schoolCode ?

? ? ,GET_JSON_OBJECT(data_tmp,'$.width') AS width

? ? ,GET_JSON_OBJECT(data_tmp,'$.subject') AS subject

? ? ,GET_JSON_OBJECT(data_tmp,'$.subjectCode') AS subjectCode

? ? ,GET_JSON_OBJECT(data_tmp,'$.classTime') AS classTime

? ? ,GET_JSON_OBJECT(data_tmp,'$.reason') AS reason

? ? ,GET_JSON_OBJECT(data_tmp,'$.operateVersion') AS operateVersion ?

? ? ----------新增---------

? ? ,CASE WHEN GET_JSON_OBJECT(data_tmp,'$.userId') ?is not NULL THEN GET_JSON_OBJECT(data_tmp,'$.userId')

? ? ? ? ? WHEN GET_JSON_OBJECT(data_tmp,'$.teacherCode') is not null THEN ?GET_JSON_OBJECT(data_tmp,'$.teacherCode')

? ? ? ? ? ELSE ?GET_JSON_OBJECT(data_tmp,'$.userId')

? ? ?END ?AS userId ? ? ? ? ?

? ? ,GET_JSON_OBJECT(data_tmp,'$.userName') AS userName

? ? ,GET_JSON_OBJECT(data_tmp,'$.userType') AS userType

? ? ,GET_JSON_OBJECT(data_tmp,'$.account') AS account

? ? ,GET_JSON_OBJECT(data_tmp,'$.courseId') AS courseId ?

? ? ,GET_JSON_OBJECT(data_tmp,'$.pageName') AS pageName ?

? ? ,GET_JSON_OBJECT(data_tmp,'$.pageTitle') AS pageTitle ?

? ? ,CASE ? ?WHEN GET_JSON_OBJECT(data_tmp,'$.describe') is not NULL ?THEN GET_JSON_OBJECT(data_tmp,'$.describe')

? ? ? ? ? ? ?WHEN GET_JSON_OBJECT(data_tmp,'$.eventDesc') is not NULL ?THEN GET_JSON_OBJECT(data_tmp,'$.eventDesc')

? ? ? ? ? ? ?ELSE ?''

? ? ? ? ? ? ?END AS ?describes ? ?

? ? ,GET_JSON_OBJECT(data_tmp,'$.source') AS source ? ? ?

? ? ,GET_JSON_OBJECT(data_tmp,'$.topDistance') AS topDistance ?

? ? ,GET_JSON_OBJECT(data_tmp,'$.size') AS sizes ?

? ?---------------json_build--------------------

? ? ,GET_JSON_OBJECT(json_build,'$.sysVersion') ? AS sysVersion ? ?

? ? ,GET_JSON_OBJECT(json_build,'$.cpuType') ? ? AS cpuType ?

? ? ,GET_JSON_OBJECT(json_build,'$.memory') AS memory

? ? ,GET_JSON_OBJECT(json_build,'$.netType') AS netType

? ? ,GET_JSON_OBJECT(json_build,'$.sysName') AS sysName

? ? ,GET_JSON_OBJECT(json_build,'$.deviceModel') AS deviceModel

? ? ,GET_JSON_OBJECT(json_build,'$.deviceNo') AS deviceNo ?

? ?-------------------新增--------------------

? ?,GET_JSON_OBJECT(json_build,'$.screenHeight') AS screenHeight ?

? ?,GET_JSON_OBJECT(json_build,'$.screenWidth') AS screenWidth ?

? ?,GET_JSON_OBJECT(json_build,'$.browserName') AS browserName

? ?,GET_JSON_OBJECT(json_build,'$.browserVersion') AS browserVersion

? ?,GET_JSON_OBJECT(json_build,'$.browserWidth') AS browserWidth

? ?,GET_JSON_OBJECT(json_build,'$.browserHeight') AS browserHeight

? ?,GET_JSON_OBJECT(json_build,'$.ip') AS ip

? ?,GET_JSON_OBJECT(json_build,'$.remoteIp') AS remoteIp

? ?,GET_JSON_OBJECT(data_tmp,'$.actionName') AS actionName

? ?,GET_JSON_OBJECT(data_tmp,'$.finishStatus') AS finishStatus

? ?,GET_JSON_OBJECT(data_tmp,'$.isFirst') AS isFirst

? ?,GET_JSON_OBJECT(data_tmp,'$.bankType') AS bankType

? ?,GET_JSON_OBJECT(data_tmp,'$.book') AS book

? ?,GET_JSON_OBJECT(data_tmp,'$.mode') AS mode

? ?,GET_JSON_OBJECT(data_tmp,'$.chapter') AS chapter

? ?,GET_JSON_OBJECT(data_tmp,'$.result') AS result

? ?,GET_JSON_OBJECT(data_tmp,'$.knowledgeCount') AS knowledgeCount

? ?,GET_JSON_OBJECT(data_tmp,'$.questCount') AS questCount

? ?,GET_JSON_OBJECT(data_tmp,'$.scoreType') AS scoreType

? ?,GET_JSON_OBJECT(data_tmp,'$.scoreModule') AS scoreModule

? ?,GET_JSON_OBJECT(data_tmp,'$.appName') AS appName

? ?,GET_JSON_OBJECT(data_tmp,'$.voteNumber') AS voteNumber

? ?,GET_JSON_OBJECT(data_tmp,'$.perVoteNubmer') AS perVoteNubmer

? ?,GET_JSON_OBJECT(data_tmp,'$.type') AS attributeType

--- 新增 2022-12-09 ----

? ?,GET_JSON_OBJECT(data_tmp,'$.loginTypeName') AS loginTypeName

? ?,GET_JSON_OBJECT(data_tmp,'$.name') AS noteName

? ?,GET_JSON_OBJECT(data_tmp,'$.notes') AS notes

? ?,GET_JSON_OBJECT(data_tmp,'$.pageNum') AS pageNum

? ?,GET_JSON_OBJECT(data_tmp,'$.color') AS color ?

? ?,GET_JSON_OBJECT(data_tmp,'$.event') AS event

? ?,GET_JSON_OBJECT(data_tmp,'$.date') AS ?switchDate

? ?,GET_JSON_OBJECT(data_tmp,'$.input') AS ?inputValue ?

? ?,GET_JSON_OBJECT(data_tmp,'$.title') AS ?title

? ?,GET_JSON_OBJECT(data_tmp,'$.fileName') AS ?fileName ? --文件名

-- 1.文檔：doc、docx、PDF

-- 2.音頻：WAV、ape、AIFF、CD、AU、MP3、WMA、VQF、FLAC、MIDI、Ogg、U-Law、VOC、aac、RA/.RM/.RAM

-- 3.視頻：avi、MOV/.QT、MKV、MP4、WMV、MPEG、BD、HDVD、RMVB、PROPER、R5、Watermarks、TS、DAT、SWF、ASF、3GP、FLV、HDRIP、IMAX

-- 4.課件：ppt、pptx、pps、ppsx、ppa、ppam、pot、potx、thmx

-- 5.圖片：Webp、BMP、PCX、TIF、GIF、JPEG、TGA、EXIF、FPX、SVG、PSD、CDR、PCD、DXF、UFO、EPS、AI、PNG、HDRI、RAW、WMF、FLIC、EMF、ICO

-- 6.表格：xls、csv、CSS、XPS、xlsm、et、

-- 7.壓縮包：RAR、ZIP、ARJ、Z、LZH、JAR

-- 8.其他

? ?,GET_JSON_OBJECT(data_tmp,'$.fileId') AS ?fileId ? ? ?

? ?,GET_JSON_OBJECT(data_tmp,'$.fileNames') AS ?fileNames ?

? ?,GET_JSON_OBJECT(data_tmp,'$.beginDate') AS ?beginDate ? ?

? ?,GET_JSON_OBJECT(data_tmp,'$.endDate') AS ?endDate ?

? ?,GET_JSON_OBJECT(data_tmp,'$.questionId') AS ?questionId ? --題號(hào)

? ?,GET_JSON_OBJECT(data_tmp,'$.packageName') AS ?packageName ?

? ?,GET_JSON_OBJECT(data_tmp,'$.versionName') AS ?versionName ? ?

? ?,GET_JSON_OBJECT(data_tmp,'$.versionCode') AS ?versionCode

? ?,GET_JSON_OBJECT(data_tmp,'$.jobId') AS ?jobId

? ?,GET_JSON_OBJECT(data_tmp,'$.answer') AS ?answer

? ?,GET_JSON_OBJECT(data_tmp,'$.wrong') AS ?wrong ? ?

? ?,GET_JSON_OBJECT(data_tmp,'$.correct') AS ?correct

? ?,GET_JSON_OBJECT(data_tmp,'$.unanswered') AS ?unanswered ?

? ?,GET_JSON_OBJECT(data_tmp,'$.finishNumber') AS ?finishNumber ?

? ?,GET_JSON_OBJECT(data_tmp,'$.totalNumber') AS ?totalNumber ?

? ?,GET_JSON_OBJECT(data_tmp,'$.word') AS ?word ?

? ?,GET_JSON_OBJECT(data_tmp,'$.msg') AS ?msg ?

? ?,GET_JSON_OBJECT(data_tmp,'$.count') AS ?impCount ?

? ?,GET_JSON_OBJECT(json_build,'$.pageHeight') AS pageHeight ? --頁(yè)面高度

? ?,GET_JSON_OBJECT(data_tmp,'$.answers') AS ?answers --答題情況

? ?--新增--

? ?,GET_JSON_OBJECT(data_tmp,'$.num') AS ?num --題目數(shù)量

? ?,GET_JSON_OBJECT(data_tmp,'$.op') AS ?op --隨機(jī)選人 選項(xiàng) op(清除(NULL)、A、B、C) op(不隨機(jī)(0)、1、2、3)

? ?,GET_JSON_OBJECT(data_tmp,'$.leaveTime') AS ?leaveTime --收卷倒計(jì)時(shí)

? ?,GET_JSON_OBJECT(data_tmp,'$.examId') AS ?examId --考試id

? ?,GET_JSON_OBJECT(data_tmp,'$.id') AS ?idCode --id，用英文逗號(hào)隔開，組code

? ?,GET_JSON_OBJECT(data_tmp,'$.code') AS ?code --對(duì)調(diào)學(xué)生

? ?,GET_JSON_OBJECT(data_tmp,'$.rol') AS ?rol --對(duì)調(diào)學(xué)生 位置rol

? ?,GET_JSON_OBJECT(data_tmp,'$.col') AS ?col --對(duì)調(diào)學(xué)生 位置col

? ?,GET_JSON_OBJECT(data_tmp,'$.stage') AS ?stage --學(xué)段

? ?,GET_JSON_OBJECT(data_tmp,'$.version') AS ?versions --學(xué)段

-- ? ?,GET_JSON_OBJECT(data_tmp,'$.type') AS ?見 attributeType --類型 type(1.批注作答 2.畫板作答)

? -- ,GET_JSON_OBJECT(data_tmp,'$.actionName') AS actionName ? ? ?--活動(dòng)名稱

? -- ,GET_JSON_OBJECT(data_tmp,'$.answer') AS ?answer ? ?--答案

? ?--,GET_JSON_OBJECT(data_tmp,'$.color') AS ?color --顏色

? -- ,GET_JSON_OBJECT(data_tmp,'$.finishNumber') AS ?finishNumber --找到的數(shù)量

? ?--,GET_JSON_OBJECT(data_tmp,'$.totalNumber') AS ?totalNumber --總詞數(shù)

? -- ,GET_JSON_OBJECT(data_tmp,'$.word') AS ?word --未答數(shù)

? -- `completionStatus` varchar(100) DEFAULT NULL COMMENT '完成情況',

?-- ,GET_JSON_OBJECT(data_tmp,'$.event') AS ?event --收起/展開事件

? --,GET_JSON_OBJECT(data_tmp,'$.fileId') AS ?fileId --云端文件ID

? --,GET_JSON_OBJECT(data_tmp,'$.fileNames') AS ?fileNames --文件名列表

? --,GET_JSON_OBJECT(data_tmp,'$.fileName') AS ?fileName --文件名

? --,GET_JSON_OBJECT(data_tmp,'$.date') AS ? ?dates --日期篩選類型

? --,GET_JSON_OBJECT(data_tmp,'$.result') AS ?isfinish --完成情況 result：true/false

?-- ,GET_JSON_OBJECT(data_tmp,'$.input') AS ?inputValue --輸入值

? --,GET_JSON_OBJECT(data_tmp,'$.jobId') AS ?jobId --作業(yè)ID

? --,GET_JSON_OBJECT(data_tmp,'$.name') AS ?name --名稱

? --,GET_JSON_OBJECT(data_tmp,'$.questionId') AS ?questionId --題目ID

? --`jobType` varchar(50) DEFAULT NULL COMMENT '作業(yè)類型',

? --`noteName` varchar(50) DEFAULT NULL COMMENT '筆記本名稱',

? --`signName` varchar(100) DEFAULT NULL COMMENT '標(biāo)簽名', ?

? --`switchDate` datetime DEFAULT NULL COMMENT '日期切換日期值',

? --`thickNess` varchar(10) DEFAULT NULL COMMENT '粗細(xì)值',

? --`timeSlot` varchar(10) DEFAULT NULL COMMENT '時(shí)間段',

? --`toolName` varchar(50) DEFAULT NULL COMMENT '工具名稱',

? --`wrongBookName` varchar(50) DEFAULT NULL COMMENT '錯(cuò)題本名稱',

-- ?,GET_JSON_OBJECT(json_build,'$.loginTypeName') AS appcode

-- ? ?,GET_JSON_OBJECT(json_build,'$.appVersion') AS appVersion

,createtime ?--創(chuàng)建時(shí)間

from (

select ?theid,

? ? ? ? ? ? ? ? ? ? id,

? ? ? ? ? ? ? ? ? ? name,

? ? ? ? ? ? ? ? ? ? eventtime,

? ? ? ? ? ? ? ? ? ? apptype,

? ? ? ? ? ? ? ? ? ? ?regexp_replace(regexp_replace(regexp_replace(build,'^\\[',''),'\\]$',''),'},\\{','}|{') AS json_build,

? ? ? ? ? ? ? ? ? ? spanid,

? ? ? ? ? ? ? ? ? ? type,

? ? ? ? ? ? ? ? ? ? appcode,

? ? ? ? ? ? ? ? ? ? regexp_replace(regexp_replace(regexp_replace(data,'^\\[',''),'\\]$',''),'},\\{','}|{') AS json_data,

? ? ? ? ? ? ? ? ? ? appversion,

? ? ? ? ? ? ? ? ? ? appnum,

? ? ? ? ? ? ? ? ? ? preid,

? ? ? ? ? ? ? ? ? ? traceid,

? ? ? ? ? ? ? ? ? ? createtime

? ? ? ? ? ? ? ? ? ? ?from ?dw_es_action_log_inc_new

? ? ? ? ? ? ? ? ? ? WHERE

? ? ? ? ? ? ? ? ? ?-- ?DATETRUNC(eventtime,'hh') >= ?DATETRUNC(dateadd(TO_DATE('${cyctime}','yyyymmddhhmiss'), -1, 'hh'),'hh') ? ? or ? ?

? ? ? ? ? ? ? ? ? ?

? ? ? ? ? ? ? ? ? ? ?DATETRUNC(createtime,'DD') >= ?DATETRUNC(TO_DATE('${bizdate}','yyyymmdd'),'DD') ?--測(cè)試使用 ? ? ? ? ? ?

) a0

lateral view explode(split(json_data,'\\|')) b AS data_tmp;

從中可以發(fā)現(xiàn)，針對(duì)很多不同格式的 數(shù)據(jù)，可以進(jìn)行這種分解處理。

select bh,bjmc,nj,xxbm,xxmc,xnid,xn,xnmc,xd,rnk from (SELECT bh,bjmc,nj,xxbm,xxmc,xnid,xn,xnmc,xd, Row_Number() OVER (partition by bh,xxbm ORDER BY nj desc) rnk

FROM dw_class where ?zt='1' ?and ?bjlxm = '1' and xnid <> '' ) aa where rnk='1' ?

同時(shí)可以使用Row_Number，進(jìn)行數(shù)據(jù)處理，獲取最大年級(jí)數(shù)據(jù)。