常用SQL

SELECT DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%s')mydate, count(1)num from payment_flow  where create_time>='2023-05-15'
group by mydate order by num desc limit 10;

SELECT rid, id,create_time,query_time,callback_time,
       TIMESTAMPDIFF(SECOND, query_time,callback_time )diff
from pay_query_order where create_time>'2023-05-15' order by diff desc limit 50;

SELECT rid, id,create_time,query_time,callback_time,
       TIMESTAMPDIFF(SECOND, create_time,query_time )diff from pay_query_order 
where create_time>current_date order by diff desc limit 200;

SELECT 
    id, 
    create_time, 
    channel, 
    CASE WHEN channel = 'xxx' THEN callback_time ELSE '' END AS callback_time,
    amount,
    merchant_id 
FROM payment_flow 
WHERE create_time > 'xxx 
LIMIT 5;

最近1小时成功率

SELECT
    date_format(now(),'%Y%m%d %H:%i') mydate,
    accountid,
    COUNT(1) AS total_order_count,
    COUNT(CASE WHEN status = 'SUCCESS' THEN 1 END) AS success_order_count,
    CAST(COUNT(CASE WHEN status = 'SUCCESS' THEN 1 END) AS DECIMAL(10, 2)) / COUNT(1) AS success_rate, 
    CONCAT(FORMAT(COUNT(CASE WHEN status = 'SUCCESS' THEN 1 END) / COUNT(1) * 100, 2), '%') AS success_rate_str
FROM
    mytable
WHERE
    create_time > DATE_SUB(NOW(), INTERVAL 1 HOUR)
GROUP BY
    accountid;

select id,create_time,update_time,callback_time from payment_flow where create_time>='2023-05-21' and create_time<'2023-05-22' and status='SUCCESS' and callback_time > create_time + INTERVAL 12 HOUR;

SELECT pua.*
FROM payment_unsettle_all AS pua
LEFT JOIN payment_flow AS pf ON pf.id = pua.id AND pf.is_settlement = '1'
LEFT JOIN settlement_payment_detail AS spd ON spd.order_id = pua.id
WHERE pua.is_settlement = '0'
AND (pf.id IS NOT NULL OR spd.order_id IS NOT NULL);

select concat(tjdate, ' ', hour) as time,
       cc.name,
       m.name,
       sum(payment_amount),
       CONCAT(FORMAT(sum(payment_num_succ) / sum(payment_num) * 100, 2), '%') AS success_rate_str
from mytable a
         left join merchant m on m.id = a.merchant_id
         left join channel_config cc on a.accountid = cc.account_id
where tjdate >= '2023-07-05'
  and tjdate < '2023-07-12'
group by m.name, cc.name, concat(tjdate, ' ', hour)
order by time desc;

select concat(tjdate, ' ', hour)                                              as time,
       cc.name,
       m.name,
       sum(payment_num),
       sum(payment_num_succ),
       sum(payment_amount),
       CONCAT(FORMAT(sum(payment_num_succ) / sum(payment_num) * 100, 2), '%') AS success_rate_str
from payment_tj_dayhour a
         left join merchant m on m.id = a.merchant_id
         left join channel_config cc on a.accountid = cc.account_id
where tjdate = '2024-03-20'
  and merchant_id = '64c9c3a1c3976b2d12493494'
group by m.name, cc.name, concat(tjdate, ' ', hour)
order by time desc;