Приветствую.
SQL-запрос КУБа:
WITH Q AS( --Базовый запрос
SELECT
YEAR_NCODE,
MONTHOFYEAR_NCODE,
YEAR_NCODE*12+MONTHOFYEAR_NCODE as year_month_ncode, --Номер скользящего месяца
MEASURE_CCODE,
BUSINESS_NAME,
PRODUCT_NAME,
MACROREGION_NAME,
SERVICE_NAME,
CLIENTTYPE_NAME,
CLIENTSEGMENT_NAME,
ANLGRP_CLIENTSEGMENT_CCODE,
CHANNELDETAIL_NAME,
ANALYTICALDIMENSION_THREE_NAME,
FACTCOMPARISON_FLAG,
FACT_1_VALUE,
FACT_PY_1_VALUE
FROM dmgd.TOPMANAGERMEASURESENR_VW_RPT
WHERE REPORT_DATE >= '2021-01-01'
AND PERIODTYPE_CCODE='M'
AND PRODUCT_NAME IS NOT NULL
AND VALUESTREAM_NAME = 'Письма'
AND MEASURE_CCODE IN ('PL_STREAM', 'CLIENT_CNT', 'RPO_CNT')
AND ${filters()}
),
vMaxYearT AS ( --Определяем МАХ год
SELECT
Max(YEAR_NCODE) as vMaxYear
FROM
Q
WHERE FACT_1_VALUE != 0
),
vMaxMonthT AS ( --Определяем МАХ месяц в МАХ году
SELECT
Max(MONTHOFYEAR_NCODE) as vMaxMonth
FROM
Q, vMaxYearT
WHERE YEAR_NCODE = vMaxYear AND FACT_1_VALUE != 0
)
SELECT
YEAR_NCODE,
MONTHOFYEAR_NCODE,
year_month_ncode,
MEASURE_CCODE,
BUSINESS_NAME,
PRODUCT_NAME,
MACROREGION_NAME,
SERVICE_NAME,
CLIENTTYPE_NAME,
CLIENTSEGMENT_NAME,
ANLGRP_CLIENTSEGMENT_CCODE,
CHANNELDETAIL_NAME,
ANALYTICALDIMENSION_THREE_NAME,
FACTCOMPARISON_FLAG,
FACT_1_VALUE,
FACT_PY_1_VALUE,
vMaxYear,
vMaxMonth
FROM Q, vMaxYearT, vMaxMonthT
JSON дэша:
{
frame: {
h: 4,
w: 10,
x: 2,
y: 4,
},
dataSource: {
koob: 'clickhouse.dmgd_TOPMANAGERMEASURESENR_VW_RPT',
style: {},
xAxis: 'year_month',
yAxis: 'measures',
measures: [
"sum(if(MEASURE_CCODE='PL_STREAM' and PRODUCT_NAME='Бокс-сервис',fact_year_month)):sum_fact_year_month",
"sum(if(MEASURE_CCODE='PL_STREAM' and PRODUCT_NAME='Бокс-сервис',fact_year_month_py)):sum_fact_year_month_py",
],
dimensions: [
'year_month',
],
},
view_class: 'koob-table-simple',
title: '',
}
При этом, если это плоска таблица, то всё выводится:
Если же это столбики/линии и т.п., то попадаем на ошибку:
"details": {
"detailedMessage": "Code: 47. DB::Exception: Missing columns: 'vMaxMonth' 'vMaxYear' while processing query: 'SELECT YEAR_NCODE, MONTHOFYEAR_NCODE, (YEAR_NCODE * 12) + MONTHOFYEAR_NCODE AS year_month_ncode.......