private Select<? extends Record> getLatestTimestampQueryAllColumnsSubQuery(DSLContext ctx, TableReference tableRef, Select<Record1<byte[]>> subQuery, long timestamp) { return ctx.select(A_ROW_NAME, A_COL_NAME, DSL.max(A_TIMESTAMP).as(MAX_TIMESTAMP)) .from(atlasTable(tableRef).as(ATLAS_TABLE)) .where(A_ROW_NAME.in(subQuery) .and(A_TIMESTAMP.lessThan(timestamp))) .groupBy(A_ROW_NAME, A_COL_NAME); }
private Select<? extends Record> getLatestTimestampQueryAllColumns(DSLContext ctx, TableReference tableRef, Collection<byte[]> rows, long timestamp) { return ctx.select(A_ROW_NAME, A_COL_NAME, DSL.max(A_TIMESTAMP).as(MAX_TIMESTAMP)) .from(atlasTable(tableRef).as(ATLAS_TABLE)) .where(A_ROW_NAME.in(rows) .and(A_TIMESTAMP.lessThan(timestamp))) .groupBy(A_ROW_NAME, A_COL_NAME); }
private Select<? extends Record> getLatestTimestampQuerySomeColumnsSubQuery(DSLContext ctx, TableReference tableRef, Select<Record1<byte[]>> subQuery, Collection<byte[]> cols, long timestamp) { return ctx.select(A_ROW_NAME, A_COL_NAME, DSL.max(A_TIMESTAMP).as(MAX_TIMESTAMP)) .from(atlasTable(tableRef).as(ATLAS_TABLE)) .where(A_ROW_NAME.in(subQuery) .and(A_COL_NAME.in(cols))) .and(A_TIMESTAMP.lessThan(timestamp)) .groupBy(A_ROW_NAME, A_COL_NAME); }
private Select<? extends Record> getLatestTimestampQuerySomeColumns(DSLContext ctx, TableReference tableRef, Collection<byte[]> rows, Collection<byte[]> cols, long timestamp) { return ctx.select(A_ROW_NAME, A_COL_NAME, DSL.max(A_TIMESTAMP).as(MAX_TIMESTAMP)) .from(atlasTable(tableRef).as(ATLAS_TABLE)) .where(A_ROW_NAME.in(rows) .and(A_COL_NAME.in(cols))) .and(A_TIMESTAMP.lessThan(timestamp)) .groupBy(A_ROW_NAME, A_COL_NAME); }
private Select<? extends Record> getLatestTimestampQueryManyTimestamps(DSLContext ctx, TableReference tableRef, RowN[] rows) { return ctx.select(A_ROW_NAME, A_COL_NAME, DSL.max(A_TIMESTAMP).as(MAX_TIMESTAMP)) .from(atlasTable(tableRef).as(ATLAS_TABLE)) .join(values(ctx, rows, TEMP_TABLE_1, ROW_NAME, COL_NAME, TIMESTAMP)) .on(A_ROW_NAME.eq(T1_ROW_NAME) .and(A_COL_NAME.eq(T1_COL_NAME))) .where(A_TIMESTAMP.lessThan(T1_TIMESTAMP)) .groupBy(A_ROW_NAME, A_COL_NAME); }
/** * This method does not filter the tags based on the data point permissions. It should only be used * when joining onto the data points table (the filtering happens there post-join). * * @param tagKeyToColumn * @return */ Select<Record> createTagPivotSql(Map<String, Name> tagKeyToColumn) { List<Field<?>> fields = new ArrayList<>(tagKeyToColumn.size() + 1); fields.add(DATA_POINT_ID); for (Entry<String, Name> entry : tagKeyToColumn.entrySet()) { fields.add(DSL.max(DSL.when(TAG_KEY.eq(entry.getKey()), TAG_VALUE)).as(entry.getValue())); } return DSL.select(fields).from(DATA_POINT_TAGS).groupBy(DATA_POINT_ID); }
@Override public PageResult<Tag> page(PageResult<Tag> page) { return dao.fetch(page, e -> { return e.select(Fields.all(C_TAG.fields(), DSL.count(C_ARTICLE_TAG.ARTICLE_ID).as("articleCount"))).from(C_TAG,C_ARTICLE_TAG) .where(C_TAG.ID.eq(C_ARTICLE_TAG.TAG_ID)) .groupBy(C_ARTICLE_TAG.TAG_ID); }, Tag.class); } }
sum(when(a.STATUS.eq(RUNNING.name()), 1).otherwise(0)).as(RUNNING.name()), sum(when(a.STATUS.eq(CHANGED.name()), 1).otherwise(0)).as(CHANGED.name()), sum(when(a.STATUS.eq(FAILED.name()), 1).otherwise(0)).as(FAILED.name()), sum(when(a.STATUS.eq(OK.name()), 1).otherwise(0)).as(OK.name()), sum(when(a.STATUS.eq(SKIPPED.name()), 1).otherwise(0)).as(SKIPPED.name()), sum(when(a.STATUS.eq(UNREACHABLE.name()), 1).otherwise(0)).as(UNREACHABLE.name()), arrayAggDistinct(a.HOST_GROUP)) .from(a)
public List<Catalog> findAllDetail() { return dao.execute(e -> { return e.select(Fields.all(C_CATALOG.fields(), DSL.count(C_ARTICLE.ID).as("articleCount"))).from(C_ARTICLE) .leftJoin(C_CATALOG).on(C_CATALOG.ID.eq(C_ARTICLE.CATALOG_ID)) .where(C_ARTICLE.STATUS.eq(Article.STATUS_PUBLISH)) .groupBy(C_CATALOG.ID) .orderBy(C_CATALOG.SORT).fetch(r -> { return dao.mapperEntityEx(r, Catalog.class); }); }); } }
.with(project_info) .with(project_cost) .select(project_name, total_cost, company_name, sum(monthly_cost).as("company_cost")) .from(project_info) .join(project_cost).using(project_info.field("project_pid"))