如何在Jooq中按月份和年份将millis翻译为日期和组

Question

我有以下SQL，它按预期工作：

select  YEAR(CONVERT_TZ(FROM_UNIXTIME(creation_date / 1000), @@session.time_zone, "Europe/Berlin")) as year,
MONTH(CONVERT_TZ(FROM_UNIXTIME(creation_date / 1000), @@session.time_zone, "Europe/Berlin")) as month,
sum(value)
from transaction
GROUP BY year, month; 

我正试图在Jooq中重新创建这些SQL，但我不知道如何根据数据库中creation_date的毫秒数创建Date对象。

dsl.select(DSL.month(DSL.date(TRANSACTION.CREATION_DATE)), // This does not work
DSL.year(DSL.date(TRANSACTION.CREATION_DATE)), // This does not work
DSL.sum(TRANSACTION.VALUE))
.from(TRANSACTION)
.groupBy(???); // How to group by month and year?

Answer 1

在编写SQLGROUP BY时，常见的混淆是SQL操作的逻辑顺序。虽然从句法上看，SELECT似乎出现在GROUP BY之前，但从逻辑上讲，顺序是相反的。这意味着您不能真正从GROUP BY子句引用SELECT子句中的列。

一些方言可能"为了方便"而实施了例外，但这通常非常令人困惑。我建议不要那样做。

但要解决您的问题：

生成要生成的SQL

在最初的SQL查询中，您为两个表达式(AS year和AS month(使用了别名，而在jOOQ查询中则没有。我建议您也使用别名，并将列表达式分配给局部变量，以便在groupBy()子句中重用：

Field<?> month = DSL.month(DSL.date(TRANSACTION.CREATION_DATE)).as("month");
Field<?> year  = DSL.year(DSL.date(TRANSACTION.CREATION_DATE)).as("year");
dsl.select(month, year, DSL.sum(TRANSACTION.VALUE))
.from(TRANSACTION)
.groupBy(month, year)
.fetch();

别名列在SELECT子句中生成完整声明，但在所有其他子句中只生成别名，因此这正是您想要的：

SELECT
month(date(transaction.creation_date)) as month,
year(date(transaction.creation_date)) as year
sum(transaction.value)
FROM transaction
GROUP BY
month,
year;

根据操作的逻辑顺序生成更好的SQL语句

如果您希望您的SQL根据我提到的SQL操作的逻辑顺序保持可移植性和正确性，我建议您改为写以下内容：

Field<?> month = DSL.month(DSL.date(TRANSACTION.CREATION_DATE));
Field<?> year  = DSL.year(DSL.date(TRANSACTION.CREATION_DATE));
dsl.select(month.as("month"), year.as("year"), DSL.sum(TRANSACTION.VALUE))
.from(TRANSACTION)
.groupBy(month, year)
.fetch();

请注意，我已经将别名移到了SELECT子句中，而在GROUP BY子句中，我现在引用的是整列表达式。这将产生以下查询：

SELECT
month(date(transaction.creation_date)) as month,
year(date(transaction.creation_date)) as year
sum(transaction.value)
FROM transaction
GROUP BY
month(date(transaction.creation_date)),
year(date(transaction.creation_date));

完整的表达式现在扩展到GROUP BY子句中，而不必手动重复它们。