postgresql按照年月日统计历史数据

1.按照日

SELECT a.time,COALESCE(b.counts,0) as counts from
(
SELECT
to_char ( b, 'YYYY-MM-DD' ) AS time
FROM
generate_series ( to_timestamp ( '2024-06-01', 'YYYY-MM-DD hh24:mi:ss' ), to_timestamp ( '2024-06-30', 'YYYY-MM-DD hh24:mi:ss' ), '1 days' ) AS b
GROUP BY
time ORDER BY time asc

) as a

FULL OUTER JOIN

(
select to_char(to_timestamp(create_time/1000)::TIMESTAMP, 'YYYY-MM-DD' ) AS starttime, count(*) as counts from t_work_order GROUP BY starttime
) as b
on a.time=b.starttime
WHERE time is not null
order by a.time asc

2.按照月

SELECT a.time,COALESCE(b.counts,0) as counts from
(
SELECT
to_char ( b, 'YYYY-MM' ) AS time
FROM
generate_series ( to_timestamp ( '2024-01-01', 'YYYY-MM' ), to_timestamp ( '2024-06-30', 'YYYY-MM' ), '1 months' ) AS b
GROUP BY
time ORDER BY time asc

) as a

FULL OUTER JOIN

(
select to_char(to_timestamp(create_time/1000)::TIMESTAMP, 'YYYY-MM' ) AS starttime, count(*) as counts from t_work_order GROUP BY starttime
) as b
on a.time=b.starttime
WHERE time is not null
order by a.time asc

3.按照年

SELECT a.time,COALESCE(b.counts,0) as counts from
(
SELECT
to_char ( b, 'YYYY' ) AS time
FROM
generate_series ( to_timestamp ( '2022-01-01', 'YYYY' ), to_timestamp ( '2024-06-30', 'YYYY' ), '1 years' ) AS b
GROUP BY
time ORDER BY time asc

) as a

FULL OUTER JOIN

(
select to_char(to_timestamp(create_time/1000)::TIMESTAMP, 'YYYY' ) AS starttime, count(*) as counts from t_work_order GROUP BY starttime
) as b
on a.time=b.starttime
order by a.time asc

4.表结构如下,时间为时间戳

CREATE TABLE "public"."t_work_order" (
  "id" int8 NOT NULL,
  "tenant_no" int4 NOT NULL DEFAULT 1,
  "order_code" varchar(64) COLLATE "pg_catalog"."default",
  "order_type_id" int8,
  "order_type_name" varchar(64) COLLATE "pg_catalog"."default",
  "order_type_code" varchar(64) COLLATE "pg_catalog"."default",
  "order_sub_type_id" int8,
  "order_sub_type_name" varchar(64) COLLATE "pg_catalog"."default",
  "order_sub_type_code" varchar(64) COLLATE "pg_catalog"."default",
  "order_source_id" int8,
  "order_source_name" varchar(64) COLLATE "pg_catalog"."default",
  "order_source_code" varchar(64) COLLATE "pg_catalog"."default",
  "area_id" int8,
  "area_name" varchar(64) COLLATE "pg_catalog"."default",
  "asset_id" int8,
  "asset_name" varchar(255) COLLATE "pg_catalog"."default",
  "device_id" int8,
  "device_name" varchar(255) COLLATE "pg_catalog"."default",
  "project_id" int8,
  "project_name" varchar(255) COLLATE "pg_catalog"."default",
  "description" varchar(1024) COLLATE "pg_catalog"."default",
  "risk_level_id" int8,
  "risk_level_name" varchar(64) COLLATE "pg_catalog"."default",
  "risk_level_code" varchar(64) COLLATE "pg_catalog"."default",
  "release_user_id" int8,
  "release_user_name" varchar(64) COLLATE "pg_catalog"."default",
  "release_time" int8,
  "resolver_user_id" int8,
  "resolver_user_name" varchar(64) COLLATE "pg_catalog"."default",
  "resolver_time" int8,
  "expected_processing_time" int8,
  "response_time" int8,
  "arrive_time" int8,
  "finish_time" int8,
  "start_time" int8,
  "handle_duration" int8,
  "hang_time" int8,
  "hang_duration" int8,
  "status" varchar(64) COLLATE "pg_catalog"."default",
  "extra_process_id" int8,
  "third_code" varchar(256) COLLATE "pg_catalog"."default",
  "hasten_status" int4,
  "hasten_time" int8,
  "create_time" int8,
  "create_by" varchar(64) COLLATE "pg_catalog"."default",
  "update_time" int8,
  "update_by" varchar(64) COLLATE "pg_catalog"."default",
  "del" int4,
  "release_user_phone" varchar(32) COLLATE "pg_catalog"."default",
  "resolver_user_phone" varchar(32) COLLATE "pg_catalog"."default",
  "create_user_name" varchar(64) COLLATE "pg_catalog"."default",
  "create_user_phone" varchar(32) COLLATE "pg_catalog"."default",
  "star_number" int4,
  "check_timeout_flag" int4 DEFAULT 2,
  "check_timeout_level" int4 DEFAULT 0,
  "check_timeout_start_time" int8,
  "star_number_quality" int4,
  "evaluate_task" int4,
  "organization_id" int8,
  "organization_name" varchar(255) COLLATE "pg_catalog"."default",
  "organization_area_id" int8,
  "organization_area_name" varchar(256) COLLATE "pg_catalog"."default",
  "cooperate_status" int4 DEFAULT 2,
  "cooperate_user_count" int4 DEFAULT 0,
  CONSTRAINT "pk_t_work_order" PRIMARY KEY ("id"),
  CONSTRAINT "t_work_order_order_code_key" UNIQUE ("order_code")
)
;

ALTER TABLE "public"."t_work_order" 
  OWNER TO "huishi";

CREATE INDEX "t_work_order_create_by_idx" ON "public"."t_work_order" USING btree (
  "create_by" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

CREATE INDEX "t_work_order_release_user_id_idx" ON "public"."t_work_order" USING btree (
  "release_user_id" "pg_catalog"."int8_ops" ASC NULLS LAST
);

CREATE INDEX "t_work_order_resolver_user_id_idx" ON "public"."t_work_order" USING btree (
  "resolver_user_id" "pg_catalog"."int8_ops" ASC NULLS LAST
);

COMMENT ON COLUMN "public"."t_work_order"."tenant_no" IS '租户分区标识';

COMMENT ON TABLE "public"."t_work_order" IS '工单记录表';