SELECT

INTEGER(userId) as user_id,

SEC_TO_TIMESTAMP(time + 10800) as time,

eventInfo.eventAction as eventAction,

JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.adSystem') as adSystem,

JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.externalCid') as externalCid,

device.ip as ip,

JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.type') as type,

IF (JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.count') IS NULL, NULL, INTEGER(JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.count'))) as count_r,

//с какой рекомендаций произошло некое действие

IF (JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.list') CONTAINS 'r03' OR JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.type') CONTAINS 'r03', 1, 0) as r03,

IF (JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.list') CONTAINS 'r04' OR JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.type') CONTAINS 'r04', 1, 0) as r04,

IF (JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.list') CONTAINS 'r05' OR JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.type') CONTAINS 'r05', 1, 0) as r05,

IF (JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.list') CONTAINS 'r06' OR JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.type') CONTAINS 'r06', 1, 0) as r06,

IF (JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.list') CONTAINS 'r07' OR JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.type') CONTAINS 'r07', 1, 0) as r07,

IF (JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.list') CONTAINS 'r08' OR JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.type') CONTAINS 'r08', 1, 0) as r08,

IF (JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.list') CONTAINS 'r10' OR JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.type') CONTAINS 'r10', 1, 0) as r10,

IF (JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.list') CONTAINS 'r11' OR JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.type') CONTAINS 'r11', 1, 0) as r11,

IF (JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.list') CONTAINS 'r13' OR JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.type') CONTAINS 'r13', 1, 0) as r13,

IF (JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.list') CONTAINS 'r14' OR JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.type') CONTAINS 'r14', 1, 0) as r14,

IF (JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.list') CONTAINS 'r15' OR JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.type') CONTAINS 'r15', 1, 0) as r15,

IF (JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.list') CONTAINS 'r16' OR JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.type') CONTAINS 'r16', 1, 0) as r16,

IF (JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.list') CONTAINS 'r18' OR JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.type') CONTAINS 'r18', 1, 0) as r18,

IF (JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.list') CONTAINS 'r19' OR JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.type') CONTAINS 'r19', 1, 0) as r19,

IF (JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.list') CONTAINS 'r20' OR JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.type') CONTAINS 'r20', 1, 0) as r20,

IF (JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.list') CONTAINS 'r22' OR JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.type') CONTAINS 'r22', 1, 0) as r22,

IF (JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.list') CONTAINS 'r23' OR JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.type') CONTAINS 'r23', 1, 0) as r23,

IF (JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.list') CONTAINS 'r24' OR JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.type') CONTAINS 'r24', 1, 0) as r24,

IF (JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.list') CONTAINS 'r25' OR JSON_EXTRACT_SCALAR (eventInfo.eventLabel, '$.type') CONTAINS 'r25', 1, 0) as r25,

eventInfo.eventLabel as eventLabel,

FROM

TABLE_DATE_RANGE(OWOXBI_Streaming.streaming_, TIMESTAMP('2017—10—15'), DATE_ADD(CURRENT_TIMESTAMP(), —1, "DAY"))

WHERE

eventInfo.eventCategory = 'Рекомендатор' AND type = 'event'

ORDER BY time