in web-console/src/views/explore-view/utils/table-query.ts [557:803]
function makeJoinCompareTableQueryAndHints(
options: MakeJoinCompareTableQueryAndHintsOptions,
): QueryAndHints {
const {
source,
where,
splitColumns,
timeBucket,
showColumns,
multipleValueMode,
measures,
compares,
compareTypes,
restrictTop,
maxRows,
orderBy,
totalSubQueryLimit,
useGroupingToOrderSubQueries,
topValuesK,
} = options;
let decodedOrderBy = decodeTableOrderBy(orderBy, true, splitColumns, showColumns, measures);
let effectiveOrderBy: SqlOrderByExpression;
if (decodedOrderBy) {
effectiveOrderBy = orderBy!;
} else {
effectiveOrderBy = C(
measures[0]?.name || splitColumns[0]?.name || showColumns[0]?.name,
).toOrderByExpression('DESC');
decodedOrderBy = decodeTableOrderBy(
effectiveOrderBy,
true,
splitColumns,
showColumns,
measures,
);
if (!decodedOrderBy) {
throw new Error('should never get here: must be able to decode own default order by');
}
}
const orderByCompareDuration = decodedOrderBy.orderedCompareDuration;
const orderByCompareType = decodedOrderBy.orderedCompareType;
const mainGroupByExpressions = splitColumns.map(splitColumn =>
toGroupByExpression(splitColumn, timeBucket, 't', undefined),
);
const showColumnExpressions: SqlAlias[] = showColumns.map(showColumn =>
toShowColumnExpression(showColumn, multipleValueMode, 't'),
);
const { commonWhere, mainWherePart, perCompareWhereParts } = computeWhereForCompares(
addTableScope(where, 't'),
compares,
splitColumns.some(isTimestamp) ? timeBucket : undefined,
);
const commonQuery = SqlQuery.selectStarFrom(source.as('t')).addWhere(commonWhere);
const topValuesQuery =
splitColumns.length && restrictTop !== 'never'
? SqlQuery.from(T(COMMON_NAME).as('t'))
.applyForEach(mainGroupByExpressions, (q, groupByExpression) =>
q.addSelect(groupByExpression, {
addToGroupBy: 'end',
}),
)
.changeOrderByExpression(
(decodedOrderBy.orderedSplitColumn
? toGroupByExpression(
decodedOrderBy.orderedSplitColumn,
timeBucket,
undefined,
undefined,
)
: decodedOrderBy.orderedShowColumn
? toShowColumnExpression(decodedOrderBy.orderedShowColumn, multipleValueMode, 't')
: decodedOrderBy.orderedThing.expression
)
.getUnderlyingExpression()
.toOrderByExpression('DESC'),
)
.changeLimitValue(topValuesK)
: undefined;
const safeSubQueryLimit = Math.floor(totalSubQueryLimit / (compares.length + 1));
const columnHints = makeBaseColumnHints(splitColumns, timeBucket, showColumns);
const mainQuery = SqlQuery.from(T(COMMON_NAME).as('t'))
.changeWhereExpression(mainWherePart)
.applyForEach(mainGroupByExpressions, (q, groupByExpression) =>
q.addSelect(groupByExpression, {
addToGroupBy: 'end',
}),
)
.applyForEach(showColumnExpressions, (q, showColumnExpression) =>
q.addSelect(showColumnExpression),
)
.applyForEach(measures, (q, measure) => q.addSelect(measure.expression.as(measure.name)))
.changeOrderByExpression(effectiveOrderBy)
.changeLimitValue(maxRows)
.applyIf(
orderByCompareDuration || !decodedOrderBy.orderedMeasure,
// In case where we are ordering on something other than a main measure value so either something from a compare or simply not on a measure
q =>
topValuesQuery
? q
.addInnerJoin(T(TOP_VALUES_NAME), getInnerJoinConditions(mainGroupByExpressions))
.changeOrderByExpression(undefined)
.changeLimitValue(undefined)
: q
.applyIf(orderByCompareDuration, q =>
q.changeOrderByExpression(
decodedOrderBy.orderedMeasure!.expression.toOrderByExpression('DESC'),
),
)
.changeLimitValue(safeSubQueryLimit),
);
const main = T('main');
const query = SqlQuery.from('main')
.changeWithParts([
SqlWithPart.simple(COMMON_NAME, commonQuery),
...(topValuesQuery ? [SqlWithPart.simple(TOP_VALUES_NAME, topValuesQuery)] : []),
SqlWithPart.simple('main', mainQuery),
...compares.map((compare, i) =>
SqlWithPart.simple(
`compare_${compare}`,
SqlQuery.from(T(COMMON_NAME).as('t'))
.changeWhereExpression(perCompareWhereParts[i])
.applyForEach(splitColumns, (q, splitColumn) =>
q.addSelect(toGroupByExpression(splitColumn, timeBucket, 't', compare), {
addToGroupBy: 'end',
}),
)
.applyForEach(showColumnExpressions, (q, showColumnExpression) =>
q.addSelect(showColumnExpression),
)
.applyForEach(measures, (q, measure) =>
q.addSelect(measure.expression.as(measure.name)),
)
.applyIf(
compare === orderByCompareDuration && orderByCompareType === 'value',
q =>
q
.changeOrderByExpression(
effectiveOrderBy.changeExpression(C(decodedOrderBy.orderedMeasure!.name)),
)
.changeLimitValue(maxRows),
q =>
topValuesQuery
? q.addInnerJoin(
T(TOP_VALUES_NAME),
getInnerJoinConditions(
splitColumns.map(splitColumn =>
toGroupByExpression(splitColumn, timeBucket, 't', compare),
),
),
)
: q
.changeOrderByExpression(
C(decodedOrderBy.orderedThing.name).toOrderByExpression('DESC'),
)
.changeLimitValue(safeSubQueryLimit),
),
),
),
])
.changeSelectExpressions([
...splitColumns.map(splitColumn =>
F(
'COALESCE',
main.column(splitColumn.name),
...compares.map(compare => T(`compare_${compare}`).column(splitColumn.name)),
).as(splitColumn.name),
),
...showColumns.map(showColumn =>
F(
'COALESCE',
main.column(showColumn.name).applyIf(useGroupingToOrderSubQueries, anyValue),
...compares.map(compare =>
T(`compare_${compare}`)
.column(showColumn.name)
.applyIf(useGroupingToOrderSubQueries, anyValue),
),
).as(showColumn.name),
),
...measures.map(measure =>
main
.column(measure.name)
.applyIf(useGroupingToOrderSubQueries, anyValue)
.apply(coalesce0)
.as(measure.name),
),
...compares.flatMap(compare => {
return measures.flatMap(measure => {
const measureName = measure.name;
const mainMeasure = main
.column(measureName)
.applyIf(useGroupingToOrderSubQueries, anyValue)
.apply(coalesce0);
const prevMeasure = T(`compare_${compare}`)
.column(measureName)
.applyIf(useGroupingToOrderSubQueries, anyValue)
.apply(coalesce0);
return makeCompareAggregatorsAndAddHints(
measureName,
compare,
compareTypes,
mainMeasure,
prevMeasure,
columnHints,
);
});
}),
])
.applyForEach(compares, (q, compare) =>
q.addFullJoin(
T(`compare_${compare}`),
getJoinConditions(
splitColumns.map(splitColumn =>
toGroupByExpression(splitColumn, timeBucket, 't', compare),
),
main,
T(`compare_${compare}`),
),
),
)
.applyIf(useGroupingToOrderSubQueries, q =>
q.changeGroupByExpressions(splitColumns.map((_, i) => SqlLiteral.index(i))),
)
.addOrderBy(effectiveOrderBy)
.changeLimitValue(maxRows);
for (const measure of measures) {
columnHints.set(measure.name, { group: 'Current' });
}
return {
query,
columnHints,
};
}