时间戳转日期(带时区)
-- 数据库optime字段为int类型的时间戳,这里要多一次。
to_char(CONVERT_TZ(to_date(CAST(to_char("optime",'#') AS varchar),'s'), 'GMT', 'Etc/GMT-9'),'yyyy-MM-dd HH:mm:ss')
-- 直接写死时间戳测试,少转一次类型
to_char(CONVERT_TZ(to_date('1582992000','s'), 'UTC', 'Asia/Shanghai'),'yyyy-MM-dd HH:mm:ss')
日期转时间戳(带时区)
select to_char(to_number(to_date('2020-03-01','yyyy-MM-dd','GMT+8'))/1000,'##########');
分区求值 使用last_value函数达到partition by
需求:统计货币表中,每个玩家获取钻石总和,消耗总和,以及最后剩余钻石数。
表结构:
SELECT DISTINCT a."rid" AS "rid",a."sid" AS "sid" ,a."rname" AS "rname",TOTAL_GET,TOTAL_COST,REMAIN FROM "a8_kr"."t_log_money" AS a
LEFT JOIN
(
SELECT
"rid", sum(CASE WHEN "val">0 THEN "val" ELSE 0 end) AS total_get,sum(CASE WHEN "val"<0 THEN "val" ELSE 0 end) AS total_cost,LAST_VALUE("new") WITHIN GROUP (ORDER BY "optime" ASC) AS remain
FROM
"a8_kr"."t_log_money"
WHERE "optime" >= to_number(to_date('2022-11-01','yyyy-MM-dd','GMT+9'))/1000
AND "optime" < to_number(to_date('2022-12-01','yyyy-MM-dd','GMT+9'))/ 1000
AND "moneytype"='diamond'
GROUP BY "rid"
)t
ON a."rid"=t."rid"
WHERE "optime" >= to_number(to_date('2022-11-01','yyyy-MM-dd','GMT+9'))/1000
AND "optime" < to_number(to_date('2022-12-01','yyyy-MM-dd','GMT+9'))/ 1000
AND "moneytype"='diamond';