Knex calculate avg in each month

Question

I have a column defined with type time and column defined with type date.

knex.schema.createTable('receipt', function(table) {
  ...
  table.double('money');
  table.timestamp('createdAt');
  ...
});

It stores data in 2 months.

[
   {
    "money": 18.80,
    "createdAt": "2021-12-01T22:20:00.000Z",
   },
   {
    "money": 38.80,
    "createdAt": "2021-11-01T22:20:00.000Z",
   },
]

when query and calculate the avg of money using SQL

SELECT createdMonth, avg(money) FROM 
(
    SELECT MONTH(createdAt) as `createdMonth`, money FROM receipt
) subTable
group by createdMonth

how to do that in Knexjs. Thank a lot.

Answer 1

create a view (instead of joining table, I used to create view)

async function createRoleStaffView() {
    let fields = [
        `money`,
        knex.raw(`MONTH(createdAt) as ${DISTINCT_MONTH}`),
          `createdAt`
    ];

    var viewDefinition = knex.select(fields).from("receipt");
    return knex.schema.raw('CREATE OR REPLACE VIEW ?? AS (\n' + viewDefinition + '\n)', ["receipt"]).then(() => {
        Logger.info("ResourceAccess", "[DONE]createOrReplaceView: receipt");
    });
}

and finally, query it.

knex("receipt").select(DISTINCT_MONTH).avg({ avgMoney: "money" }).groupBy(DISTINCT_MONTH);