筛选器接口
条件
原始查询sql
SELECT DATE ( rl. created_at) AS ` date ` ,
path,
rl. account_id,
org,
COUNT ( * ) AS request_count,
SUM ( IF ( rl. ` response_status` = 200 , 1 , 0 ) ) AS success_count,
SUM ( IF ( ff. id IS NOT NULL , 1 , 0 ) ) AS paid_count,
COALESCE ( JSON_EXTRACT( pricing_config, CONCAT( '$.' , JSON_QUOTE( path) ) ) , '' ) AS pricing,
SUM ( ff. amount) AS price
FROM ` request_log` rl
LEFT JOIN ` fund_flow` ff USING ( request_id)
LEFT JOIN ` pricing` ON pricing_id = pricing. id
GROUP BY ` date ` , ` path` , rl. ` account_id` , ` org`
对应sqlachemy语句
import os
from flasgger import swag_from
from . import admin_server
from flask import request
from apps import db
from . models import RequestLog, FundFlow, Pricing
from sqlalchemy import func
from sqlalchemy. sql import and_
base_dir = os. path. dirname( __file__)
demo_yml_path = os. path. join( base_dir, "docs/admin_server.yml" )
class JSONHelper ( ) :
@staticmethod
def jsonBQlist ( bqlist) :
result= [ ]
for item in bqlist:
jsondata= { }
for i in range ( item. __len__( ) ) :
tdic= { item. _fields[ i] : item[ i] }
jsondata. update( tdic)
result. append( jsondata)
return result
@admin_server. route( '/count_operator' , endpoint= "count_operator" , methods= [ 'POST' ] )
@swag_from( demo_yml_path, endpoint= "admin_server.count_operator" , methods= [ 'POST' ] )
def query_op ( ) :
"""
运营通过筛选器进行查询
:param: # 参数
{
"date_start": "2019-07-20",
"date_end": "2019-08-01",
"org": "", # 机构
"account_id": [0,1], # 客户id
"path": "" # 产品 后期可能会接收列表
}
:return: # 返回值
{
"data": {
"search_res": [
{
"account_id": 0,
"date": "2019-07-10 14:10:28",
"org": "",
"paid_count": "1",
"path": "/api/v1/pull/identity",
"price": null,
"pricing": "",
"request_count": 1,
"success_count": "0"
}
],
"total": {
"paid_count": 1,
"price": 0,
"request_count": 1,
"success_count": 0
}
}
}
"""
input_json = request. json
date_start = input_json[ "date_start" ]
date_end = input_json[ "date_end" ]
account_id = input_json[ "account_id" ]
org = input_json[ "org" ]
path = input_json[ "path" ]
search_conditions = [ ]
if date_start and date_end:
search = and_( RequestLog. created_at. between( date_start, date_end) )
search_conditions. append( search)
if account_id:
search = and_( RequestLog. account_id. in_( account_id) )
search_conditions. append( search)
if org:
search = and_( RequestLog. org == org)
search_conditions. append( search)
if path:
search = and_( RequestLog. path == path)
search_conditions. append( search)
res_sql = db. session\
. query(
RequestLog. created_at. label( "date" ) ,
RequestLog. path,
RequestLog. account_id,
RequestLog. org,
func. count( ) . label( "request_count" ) ,
func. SUM( func. if_( RequestLog. response_status == 200 , 1 , 0 ) ) . label( "success_count" ) ,
func. SUM( func. if_( FundFlow. id is not None , 1 , 0 ) ) . label( "paid_count" ) ,
func. coalesce( func. json_extract( Pricing. pricing_config, func. concat( '$.' , func. json_quote( RequestLog. path) ) ) , "" ) . label( "pricing" ) ,
func. SUM( FundFlow. amount) . label( "price" ) ,
) \
. outerjoin( FundFlow, RequestLog. request_id == FundFlow. request_id) \
. outerjoin( Pricing, FundFlow. id == Pricing. id ) \
. filter ( * tuple ( search_conditions) ) \
. group_by( "date" , RequestLog. path, RequestLog. account_id, RequestLog. org) \
. all ( )
search_res = JSONHelper. jsonBQlist( res_sql)
for index, item in enumerate ( search_res) :
item_str = JSONEncoder( ) . encode( item)
item = json. loads( item_str)
search_res[ index] = item
success_count = sum ( [ int ( k[ "success_count" ] ) for k in search_res] )
request_count = sum ( [ int ( k[ "request_count" ] ) for k in search_res] )
paid_count = sum ( [ int ( k[ "paid_count" ] ) for k in search_res] )
price = sum ( [ int ( k[ "price" ] ) for k in search_res if k[ "price" ] ] )
total = { }
total[ "request_count" ] = request_count
total[ "success_count" ] = success_count
total[ "paid_count" ] = paid_count
total[ "price" ] = price
data = {
"search_res" : search_res,
"total" : total
}
return { "data" : data}