如何用 sqlalchemy 的表达式语句，拼接成和以下 sql 等效的语句，注意不是直接拼接成 sql，而是执行效果一样的表达式语句。
SELECT
strftime(pay_audit_time, '%Y 年 %m 月') AS 日期,
COUNT(order_id) AS 签单数,
SUM(actual_pay_amount) AS 签单金额,
COUNT(CASE WHEN refund_audit_time IS NOT NULL THEN order_id ELSE NULL END) AS 退费数,
SUM(CASE WHEN refund_audit_time IS NOT NULL THEN refund_amount ELSE 0 END) AS 退费金额,
COUNT(CASE WHEN (refund_audit_time IS NOT NULL AND refund_consume_lesson_count <= 12) THEN order_id ELSE NULL END) AS "12课次及以内退费数",
SUM(CASE WHEN (refund_audit_time IS NOT NULL AND refund_consume_lesson_count <= 12) THEN refund_amount ELSE 0 END) AS "12课次及以内退费金额",
COUNT(CASE WHEN (refund_audit_time IS NOT NULL AND refund_consume_lesson_count > 0) THEN order_id ELSE NULL END) AS "非0课次退费数",
SUM(CASE WHEN (refund_audit_time IS NOT NULL AND refund_consume_lesson_count > 0) THEN refund_amount ELSE 0 END) AS "非0课次退费金额",
COUNT(CASE WHEN (refund_audit_time IS NOT NULL AND refund_consume_lesson_count = 0) THEN order_id ELSE NULL END) AS "0课次退费数",
SUM(CASE WHEN (refund_audit_time IS NOT NULL AND refund_consume_lesson_count = 0) THEN refund_amount ELSE 0 END) AS "0课次退费金额",
COUNT(CASE WHEN (refund_audit_time IS NOT NULL AND refund_consume_lesson_count = 1) THEN order_id ELSE NULL END) AS "1课次退费数",
SUM(CASE WHEN (refund_audit_time IS NOT NULL AND refund_consume_lesson_count = 1) THEN refund_amount ELSE 0 END) AS "1课次退费金额",
COUNT(CASE WHEN (refund_audit_time IS NOT NULL AND refund_consume_lesson_count = 2) THEN order_id ELSE NULL END) AS "2课次退费数",
SUM(CASE WHEN (refund_audit_time IS NOT NULL AND refund_consume_lesson_count = 2) THEN refund_amount ELSE 0 END) AS "2课次退费金额",
COUNT(CASE WHEN (refund_audit_time IS NOT NULL AND refund_consume_lesson_count BETWEEN 3 AND 6) THEN order_id ELSE NULL END) AS "3~6课次退费数",
SUM(CASE WHEN (refund_audit_time IS NOT NULL AND refund_consume_lesson_count BETWEEN 3 AND 6) THEN refund_amount ELSE 0 END) AS "3~6课次退费金额",
COUNT(CASE WHEN (refund_audit_time IS NOT NULL AND refund_consume_lesson_count BETWEEN 7 AND 12) THEN order_id ELSE NULL END) AS "7~12课次退费数",
SUM(CASE WHEN (refund_audit_time IS NOT NULL AND refund_consume_lesson_count BETWEEN 7 AND 12) THEN refund_amount ELSE 0 END) AS "7~12课次退费金额",
COUNT(CASE WHEN (refund_audit_time IS NOT NULL AND refund_consume_lesson_count >= 13) THEN order_id ELSE NULL END) AS "13课次及以上退费数",
SUM(CASE WHEN (refund_audit_time IS NOT NULL AND refund_consume_lesson_count >= 13) THEN refund_amount ELSE 0 END) AS "13课次及以上退费金额",
FROM
filtered_df
GROUP BY
日期
ORDER BY
日期 ASC
不妨试试以下的语句：
使用 SQLAlchemy 表达式构建查询
with Session(engine) as session:
query = (
select(
func.strftime('%Y 年 %m 月', FilteredDF.pay_audit_time).label('日期'),
func.count(FilteredDF.order_id).label('签单数'),
func.sum(FilteredDF.actual_pay_amount).label('签单金额'),
func.count(case([(FilteredDF.refund_audit_time.isnot(None), FilteredDF.order_id)])).label('退费数'),
func.sum(case([(FilteredDF.refund_audit_time.isnot(None), FilteredDF.refund_amount)], else_=0)).label('退费金额'),
func.count(case([(FilteredDF.refund_audit_time.isnot(None) & (FilteredDF.refund_consume_lesson_count <= 12), FilteredDF.order_id)])).label('12课次及以内退费数'),
func.sum(case([(FilteredDF.refund_audit_time.isnot(None) & (FilteredDF.refund_consume_lesson_count <= 12), FilteredDF.refund_amount)], else_=0)).label('12课次及以内退费金额'),
func.count(case([(FilteredDF.refund_audit_time.isnot(None) & (FilteredDF.refund_consume_lesson_count > 0), FilteredDF.order_id)])).label('非0课次退费数'),
func.sum(case([(FilteredDF.refund_audit_time.isnot(None) & (FilteredDF.refund_consume_lesson_count > 0), FilteredDF.refund_amount)], else_=0)).label('非0课次退费金额'),
func.count(case([(FilteredDF.refund_audit_time.isnot(None) & (FilteredDF.refund_consume_lesson_count == 0), FilteredDF.order_id)])).label('0课次退费数'),
func.sum(case([(FilteredDF.refund_audit_time.isnot(None) & (FilteredDF.refund_consume_lesson_count == 0), FilteredDF.refund_amount)], else_=0)).label('0课次退费金额'),
func.count(case([(FilteredDF.refund_audit_time.isnot(None) & (FilteredDF.refund_consume_lesson_count == 1), FilteredDF.order_id)])).label('1课次退费数'),
func.sum(case([(FilteredDF.refund_audit_time.isnot(None) & (FilteredDF.refund_consume_lesson_count == 1), FilteredDF.refund_amount)], else_=0)).label('1课次退费金额'),
func.count(case([(FilteredDF.refund_audit_time.isnot(None) & (FilteredDF.refund_consume_lesson_count == 2), FilteredDF.order_id)])).label('2课次退费数'),
func.sum(case([(FilteredDF.refund_audit_time.isnot(None) & (FilteredDF.refund_consume_lesson_count == 2), FilteredDF.refund_amount)], else_=0)).label('2课次退费金额'),
func.count(case([(FilteredDF.refund_audit_time.isnot(None) & (FilteredDF.refund_consume_lesson_count.between(3, 6)), FilteredDF.order_id)])).label('3~6课次退费数'),
func.sum(case([(FilteredDF.refund_audit_time.isnot(None) & (FilteredDF.refund_consume_lesson_count.between(3, 6)), FilteredDF.refund_amount)], else_=0)).label('3~6课次退费金额'),
func.count(case([(FilteredDF.refund_audit_time.isnot(None) & (FilteredDF.refund_consume_lesson_count.between(7, 12)), FilteredDF.order_id)])).label('7~12课次退费数'),
func.sum(case([(FilteredDF.refund_audit_time.isnot(None) & (FilteredDF.refund_consume_lesson_count.between(7, 12)), FilteredDF.refund_amount)], else_=0)).label('7~12课次退费金额'),
func.count(case([(FilteredDF.refund_audit_time.isnot(None) & (FilteredDF.refund_consume_lesson_count >= 13), FilteredDF.order_id)])).label('13课次及以上退费数'),
func.sum(case([(FilteredDF.refund_audit_time.isnot(None) & (FilteredDF.refund_consume_lesson_count >= 13), FilteredDF.refund_amount)], else_=0)).label('13课次及以上退费金额'),
)
.group_by(func.strftime('%Y 年 %m 月', FilteredDF.pay_audit_time))
.order_by(func.strftime('%Y 年 %m 月', FilteredDF.pay_audit_time).asc())
)
