普拓网站建设全网推广服务

当前位置： 首页 > news >正文

普拓网站建设,全网推广服务,网站后台地址修改,做视频网站 视频放在哪里由于日志数据存在ES项目里#xff0c;需要从ES中获取日志进行分析#xff0c;使用SQL数据进行处理#xff0c;如下#xff1a; select traceid– STRING COMMENT 流程id, ,appnum – BIGINT COMMENT 迭代号, ,appversion –STRING COMMENT APP版本, ,appc…由于日志数据存在ES项目里需要从ES中获取日志进行分析使用SQL数据进行处理如下 select traceid–    STRING   COMMENT 流程id, ,appnum  –   BIGINT   COMMENT 迭代号, ,appversion –STRING   COMMENT APP版本,     ,appcode   – STRING   COMMENT 应用编码, ,type     –  STRING   COMMENT 类型, ,spanid   –  STRING   COMMENT 模块id,   ,apptype   – STRING   COMMENT 应用类型详情见定义, ,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   –题号 ,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   –页面高度 ,GET_JSON_OBJECT(data_tmp,\(.answers) AS  answers --答题情况 --新增-- ,GET_JSON_OBJECT(data_tmp,\).num) AS  num –题目数量 ,GET_JSON_OBJECT(data_tmp,\(.op) AS  op --随机选人 选项 op(清除(NULL)、A、B、C) op(不随机(0)、1、2、3) ,GET_JSON_OBJECT(data_tmp,\).leaveTime) AS  leaveTime –收卷倒计时 ,GET_JSON_OBJECT(data_tmp,\(.examId) AS  examId --考试id ,GET_JSON_OBJECT(data_tmp,\).id) AS  idCode –id用英文逗号隔开组code ,GET_JSON_OBJECT(data_tmp,\(.code) AS  code --对调学生 ,GET_JSON_OBJECT(data_tmp,\).rol) AS  rol –对调学生 位置rol ,GET_JSON_OBJECT(data_tmp,\(.col) AS  col --对调学生 位置col ,GET_JSON_OBJECT(data_tmp,\).stage) AS  stage –学段 ,GET_JSON_OBJECT(data_tmp,\(.version) AS  versions --学段 --    ,GET_JSON_OBJECT(data_tmp,\).type) AS  见 attributeType –类型 type(1.批注作答 2.画板作答) – ,GET_JSON_OBJECT(data_tmp,\(.actionName) AS actionName      --活动名称 -- ,GET_JSON_OBJECT(data_tmp,\).answer) AS  answer    –答案 –,GET_JSON_OBJECT(data_tmp,\(.color) AS  color --颜色 -- ,GET_JSON_OBJECT(data_tmp,\).finishNumber) AS  finishNumber –找到的数量 –,GET_JSON_OBJECT(data_tmp,\(.totalNumber) AS  totalNumber --总词数 -- ,GET_JSON_OBJECT(data_tmp,\).word) AS  word –未答数 – 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 –完成情况 resulttrue/false – ,GET_JSON_OBJECT(data_tmp,\(.input) AS  inputValue --输入值 --,GET_JSON_OBJECT(data_tmp,\).jobId) AS  jobId –作业ID –,GET_JSON_OBJECT(data_tmp,\(.name) AS  name --名称 --,GET_JSON_OBJECT(data_tmp,\).questionId) AS  questionId –题目ID –jobType varchar(50) DEFAULT NULL COMMENT 作业类型, –noteName varchar(50) DEFAULT NULL COMMENT 笔记本名称, –signName varchar(100) DEFAULT NULL COMMENT 标签名,   –switchDate datetime DEFAULT NULL COMMENT 日期切换日期值, –thickNess varchar(10) DEFAULT NULL COMMENT 粗细值, –timeSlot varchar(10) DEFAULT NULL COMMENT 时间段, –toolName varchar(50) DEFAULT NULL COMMENT 工具名称, –wrongBookName varchar(50) DEFAULT NULL COMMENT 错题本名称, –  ,GET_JSON_OBJECT(json_build,\(.loginTypeName) AS appcode --    ,GET_JSON_OBJECT(json_build,\).appVersion) AS appVersion ,createtime  –创建时间 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)  –测试使用             ) a0 lateral view explode(split(json_data,\|)) b AS data_tmp; 从中可以发现针对很多不同格式的 数据可以进行这种分解处理。 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  zt1  and  bjlxm 1 and xnid ) aa where rnk1   同时可以使用Row_Number进行数据处理获取最大年级数据。