將一個函式的使用輸出續集到另一個（date_trunc為文字）

我必須將 postgres 查詢轉換為 Sequelize 查詢。

以下查詢正在獲取每周摘要。但是開始日是星期一。

SELECT date_trunc('week', date::date) AS "weekly", COUNT(DISTINCT(date)) AS "working_days" FROM "public"."employees" AS "Employee" WHERE ("Employee"."deleted_at" IS NULL) GROUP BY "weekly" LIMIT 100;

代碼：

const dateTruncFunc = fn("date_trunc", "week", literal("date::date"));

const result = await Employee.findAll({
    limit: 100,
    attributes: [
        [dateTruncFunc, "weekly"],
        [literal("COUNT(DISTINCT(date))"), "working_days"],
    ],
    group: ["weekly"],
    raw: true,
});
console.log(result);

輸出：

[
  { weekly: 2021-11-15T00:00:00.000Z, working_days: '1' },
  { weekly: 2021-11-22T00:00:00.000Z, working_days: '1' },
  { weekly: 2021-12-13T00:00:00.000Z, working_days: '2' },
  { weekly: 2021-12-20T00:00:00.000Z, working_days: '4' },
  { weekly: 2021-12-27T00:00:00.000Z, working_days: '2' },
  { weekly: 2022-01-03T00:00:00.000Z, working_days: '3' },
  { weekly: 2022-01-10T00:00:00.000Z, working_days: '6' },
  { weekly: 2022-01-17T00:00:00.000Z, working_days: '7' },
  { weekly: 2022-01-24T00:00:00.000Z, working_days: '7' },
  { weekly: 2022-01-31T00:00:00.000Z, working_days: '1' }
]

我想查詢從星期四開始的一天

SELECT date_trunc('week', date::date) - Interval '4 days' AS "weekly", COUNT(DISTINCT(date)) AS "working_days" FROM "public"."employees" AS "Employee" WHERE ("Employee"."deleted_at" IS NULL) GROUP BY "weekly" LIMIT 100;

代碼：

const dateTruncFunc = fn("date_trunc", "week", literal("date::date"));
const intervalLiteral = literal(" - Interval '4 days'");

const result = await Employee.findAll({
    attributes: [
        [fn("concat", dateTruncFunc, intervalLiteral), "weekly"],
        [literal("COUNT(DISTINCT(date))"), "working_days"],
    ],
    group: ["weekly"],
    raw: true,
});
console.log(result);

生成的查詢：

SELECT concat(date_trunc('week', date::date), - Interval '4 days') AS "weekly", COUNT(DISTINCT(date)) AS "working_days" FROM "public"."employees" AS "Employee" WHERE ("Employee"."deleted_at" IS NULL) GROUP BY "weekly" LIMIT 100;

輸出：

[
  { weekly: '2021-11-15 00:00:00 00-4 days', working_days: '1' },
  { weekly: '2021-11-22 00:00:00 00-4 days', working_days: '1' },
  { weekly: '2021-12-13 00:00:00 00-4 days', working_days: '2' },
  { weekly: '2021-12-20 00:00:00 00-4 days', working_days: '4' },
  { weekly: '2021-12-27 00:00:00 00-4 days', working_days: '2' },
  { weekly: '2022-01-03 00:00:00 00-4 days', working_days: '3' },
  { weekly: '2022-01-10 00:00:00 00-4 days', working_days: '6' },
  { weekly: '2022-01-17 00:00:00 00-4 days', working_days: '7' },
  { weekly: '2022-01-24 00:00:00 00-4 days', working_days: '7' },
  { weekly: '2022-01-31 00:00:00 00-4 days', working_days: '1' }
]

輸出出錯了，因為我正在連接錯誤的date_trunc輸出literal。

搜索所有資源以得到相同的問題，但無法找到它。

我需要將兩個函式的字串表示合并在一起并執行它。

請幫忙！

uj5u.com熱心網友回復：

您需要將兩者date_trunc和Interval部分放入一個Literal：

const truncWithIntervalLiteral = literal("date_trunc('week', date::date),  - Interval '4 days'");

const result = await Employee.findAll({
    attributes: [
        [truncWithIntervalLiteral , "weekly"],
        [literal("COUNT(DISTINCT(date))"), "working_days"],
    ],
    group: ["weekly"],
    raw: true,
});

標籤：

上一篇：python將sqlalchemyjson陣列結果轉儲到json檔案中

下一篇：NodsJs：TypeError：無法讀取未定義的屬性（讀取“無范圍”）