Postgre SQL
9.15. JSON Functions and Operators
-- INPUT
SELECT array_to_json(array_agg(t)) FROM t
-- OUTPUT
-- [{"a":1,"b":"value1"},{"a":2,"b":"value2"},{"a":3,"b":"value3"}]
實際運用
array_agg() 做聚合，array_to_json 及 json_build_object 來產生 json 字串
若目的只是生成特定字串，CONCAT 即可
-- 生成 10 分鐘區間內的暫存表
WITH "interval_betrecordsremix" AS (
SELECT
casino_account,
casinoid,
favorable_category,
count(casino_account) as account_betting_bycat,
sum(betvalid) as account_betvalid_bycat,
sum(betresult) as account_profit_bycat
FROM betrecordsremix
--WHERE receivetime >= '{$chk_time}-05' AND receivetime < '{$chk_time}-05'
-- WHERE casino_account = 'kt120000000019' -- TEST account
GROUP BY casino_account, casinoid, favorable_category
)
-- 輸出資料 = 表 1 (10 分鐘 page 所需資訊量 = #account * #casinoid) JOIN 表 2 (account 在每個遊戲分類的投注資訊)
SELECT * FROM (
SELECT casino_account, casinoid,
count(casino_account) as account_betting,
sum(account_betvalid_bycat) as account_betvalid,
sum(account_profit_bycat) as account_profit
FROM interval_betrecordsremix
GROUP BY casino_account, casinoid) AS ACCOUNT_CASINO_TABLE
INNER JOIN (
SELECT casino_account, casinoid, array_to_json(array_agg(json_build_object(favorable_category, favorable_category_info))) AS favorable_category FROM (
SELECT
casino_account,
casinoid,
favorable_category,
json_build_object('betvalid', sum(account_betvalid_bycat), 'betprofit', sum(account_profit_bycat)) as favorable_category_info
FROM interval_betrecordsremix
GROUP BY casino_account, casinoid, favorable_category
) AS favorable_category_betinfo GROUP BY casino_account, casinoid
) ACCOUNT_CASINO_GAME_TABLE USING(casino_account, casinoid)
How to fix PostgreSQL error "duplicate key violates unique constraint"
-- SELECT MAX(id) FROM root_memberlog;
-- SELECT nextval('root_memberlog_id_seq');
SELECT setval('root_memberlog_id_seq', (SELECT MAX(id) FROM root_memberlog)+1);
Insert OR Update
$insert_or_update_sql = <<<SQL
INSERT INTO TABLENAME ($attributes_string)
VALUES($values_string)
ON CONFLICT ON CONSTRAINT root_commission_dailyreport_member_account_dailydate
DO
UPDATE
SET $set_string
;
SQL;
自動流水號
ROW_NUMBER() ORDER BY (id, date, or nothing)
大量插入，衝突時更新
這類方式關鍵字叫做 prepare
INSERT INTO distributors (did, dname)
VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;
CTE 與遞迴
Get aggregate sum of json array in Postgres NOSQL json data
WITH x AS( SELECT
'{
"id": "tran_6ac25129951962e99f28fa488993",
"amount": 1200,
"origin_amount": 3900,
"status": "partial_refunded",
"description": "Subscription#sub_a67d59efb2bcbf73485a ",
"livemode": false,
"refunds": [
{
"id": "refund_ee4192ffb6d2caa490a1",
"amount": 1200,
"status": "refunded",
"created_at": 1426412340,
"updated_at": 1426412340
},
{
"id": "refund_0e4a34e4ee7281d369df",
"amount": 1500,
"status": "refunded",
"created_at": 1426412353,
"updated_at": 1426412353
}
]
}'::json as y),
refunds AS(
SELECT json_array_elements(y->'refunds') as j FROM x)
SELECT sum((j->>'amount')::int) FROM refunds;