postgre实现按年月日周统计分组统计

const sequelize = require(‘sequelize’);

postgre 按照年分组统计

SELECT to_char(“createdAt”::DATE, ‘YYYY’) AS “year”, SUM(“money”) AS “total” FROM “reward_logs” AS “reward_log” GROUP BY “year” ORDER BY “year” DESC

(async () => {
    let list = await model.findAll({
        'attributes': [[ sequelize.literal(`to_char("createdAt"::DATE, '${'YYYY'}')`), 'year' ], [ sequelize.fn('SUM', sequelize.col('money')), 'total' ]],
        'where': {},
        'group': [ sequelize.col('year') ],
        'order': [[ sequelize.col('year'), 'DESC' ]],
    });
    console.log('list===========>', list);
})();

postgre 按照月分组统计

SELECT to_char(“createdAt”::DATE, ‘YYYY-MM’) AS “month”, SUM(“money”) AS “total” FROM “reward_logs” AS “reward_log” GROUP BY “month” ORDER BY “month” DESC;

(async () => {
    let list = await model.findAll({
        'attributes': [[ sequelize.literal(`to_char("createdAt"::DATE, '${'YYYY-MM'}')`), 'month' ], [ sequelize.fn('SUM', sequelize.col('money')), 'total' ]],
        'where': {},
        'group': [ sequelize.col('month') ],
        'order': [[ sequelize.col('month'), 'DESC' ]],
    });
    console.log('list===========>', list);
})();

postgre 按照日分组统计

SELECT to_char(“createdAt”::DATE, ‘YYYY-MM-DD’) AS “day”, SUM(“money”) AS “total” FROM “reward_logs” AS “reward_log” GROUP BY “day” ORDER BY “day” DESC;

(async () => {
    let list = await model.findAll({
        'attributes': [[ sequelize.literal(`to_char("createdAt"::DATE, '${'YYYY-MM-DD'}')`), 'day' ], [ sequelize.fn('SUM', sequelize.col('money')), 'total' ]],
        'where': {},
        'group': [ sequelize.col('day') ],
        'order': [[ sequelize.col('day'), 'DESC' ]],
    });
    console.log('list===========>', list);
})();

postgre 按照周分组统计

SELECT to_char("createdAt"::DATE-(extract(dow from "createdAt"::TIMESTAMP)-1||'day')::interval, 'YYYY-MM-DD') AS "week", SUM("money") AS "total" FROM "reward_logs" AS "reward_log" GROUP BY "week" ORDER BY "week" DESC

(async () => {
    let list = await model.findAll({
        'attributes': [[ sequelize.literal(`to_char("createdAt"::DATE-(extract(dow from "createdAt"::TIMESTAMP)-1||'day')::interval, '${'YYYY-MM-DD'}')`), 'week' ], [ sequelize.fn('SUM', sequelize.col('money')), 'total' ]],
        'where': {},
        'group': [ sequelize.col('week') ],
        'order': [[ sequelize.col('week'), 'DESC' ]],
    });
    console.log('list===========>', list);
})();

postgre 按照小时分组统计

SELECT to_char(“createdAt”::DATE, ‘YYYY-MM-DD HH24’) AS “hour”, SUM(“money”) AS “total” FROM “reward_logs” AS “reward_log” GROUP BY “hour” ORDER BY “hour” DESC

(async () => {
    let list = await model.findAll({
        'attributes': [[ sequelize.literal(`to_char("createdAt"::DATE, '${'YYYY-MM-DD HH24'}')`), 'hour' ], [ sequelize.fn('SUM', sequelize.col('money')), 'total' ]],
        'where': {},
        'group': [ sequelize.col('hour') ],
        'order': [[ sequelize.col('hour'), 'DESC' ]],
    });
    console.log('list===========>', list);
})();

postgre 按照分钟分组统计

SELECT to_char(“createdAt”::DATE, ‘YYYY-MM-DD HH24:MI’) AS “minute”, SUM(“money”) AS “total” FROM “reward_logs” AS “reward_log” GROUP BY “minute” ORDER BY “minute” DESC;

(async () => {
    let list = await model.findAll({
        'attributes': [[ sequelize.literal(`to_char("createdAt"::DATE, '${'YYYY-MM-DD HH24:MI'}')`), 'minute' ], [ sequelize.fn('SUM', sequelize.col('money')), 'total' ]],
        'where': {},
        'group': [ sequelize.col('minute') ],
        'order': [[ sequelize.col('minute'), 'DESC' ]],
    });
    console.log('list===========>', list);
})();

postgre 按照秒分组统计

SELECT to_char(“createdAt”::DATE, ‘YYYY-MM-DD HH24:MI:SS’) AS “second”, SUM(“money”) AS “total” FROM “reward_logs” AS “reward_log” GROUP BY “second” ORDER BY “second” DESC;

(async () => {
    let list = await model.findAll({
        'attributes': [[ sequelize.literal(`to_char("createdAt"::DATE, '${'YYYY-MM-DD HH24:MI:SS'}')`), 'second' ], [ sequelize.fn('SUM', sequelize.col('money')), 'total' ]],
        'where': {},
        'group': [ sequelize.col('second') ],
        'order': [[ sequelize.col('second'), 'DESC' ]],
    });
    console.log('list===========>', list);
})();

去重统计

[ sequelize.fn('COUNT', sequelize.literal('distinct teacher_id')), 'count' ]