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