def get_stock_total_by_day():
from django.db.models import Sum
from common.raw_sql import exec_sql
dates = exec_sql("select distinct(date) from stock_stockdata")
dates = [d[0] for d in dates]
stock_total = {}
for d in dates:
stock_total[d] = StockData.objects.filter(date=d).aggregate(Sum('turnover'))['turnover__sum']
return stock_total
def get_kind_total_by_day():
from django.db.models import Sum
from common.raw_sql import exec_sql
dates = exec_sql("select distinct(date) from stock_kinddata")
dates = [d[0] for d in dates]
kind_total = {}
for d in dates:
kind_total[d] = KindData.objects.filter(date=d).aggregate(Sum('turnover'))['turnover__sum']
return kind_total
def get_count_group_by_create_time(self, count=360):
'''
查询用户数量 按创建时间分组
数据格式：
[2014-01-01, 15], [2014-01-02, 23]
'''
sql = """
SELECT DATE_FORMAT(create_time, "%%Y-%%m-%%d"), COUNT(*)
FROM account_bashi.account_user
GROUP BY DATE_FORMAT(create_time, "%%Y-%%m-%%d")
LIMIT 0, %s
"""
return raw_sql.exec_sql(sql, [count], 'account')
def get_count_group_by_create_time(self, count=360):
'''
查询用户数量 按创建时间分组
数据格式：
[2014-01-01, 15], [2014-01-02, 23]
'''
sql = """
SELECT DATE_FORMAT(create_time, "%%Y-%%m-%%d"), COUNT(*)
FROM account_cheka.account_user
GROUP BY DATE_FORMAT(create_time, "%%Y-%%m-%%d")
LIMIT 0, %s
"""
return raw_sql.exec_sql(sql, [count], 'account')
def get_toady_count_group_by_create_time(self):
'''
查询当天用户数量 按创建时间分组
数据格式：
[09, 15], [10, 23]
'''
sql = """
SELECT DATE_FORMAT(create_time, "%%H"), COUNT(*)
FROM account_cheka.account_user
WHERE %s <= create_time AND create_time <= %s
GROUP BY DATE_FORMAT(create_time, "%%H")
"""
now = datetime.datetime.now().strftime('%Y-%m-%d')
return raw_sql.exec_sql(sql, [now + ' 00:00:00', now + ' 23:59:59'], 'account')
def get_toady_count_group_by_create_time(self):
'''
查询当天用户数量 按创建时间分组
数据格式：
[09, 15], [10, 23]
'''
sql = """
SELECT DATE_FORMAT(create_time, "%%H"), COUNT(*)
FROM account_bashi.account_user
WHERE %s <= create_time AND create_time <= %s
GROUP BY DATE_FORMAT(create_time, "%%H")
"""
now = datetime.datetime.now().strftime('%Y-%m-%d')
return raw_sql.exec_sql(sql, [now + ' 00:00:00', now + ' 23:59:59'],
'account')
def get_all_dates():
from common.raw_sql import exec_sql
dates = exec_sql("select distinct(date) from stock_stockdata")
dates = [d[0] for d in dates]
return dates
def get_all_dates():
from common.raw_sql import exec_sql
dates = exec_sql("select distinct(date) from stock_stockdata")
dates = [d[0] for d in dates]
return dates