sql

＃＃＃查询消费记录

Credit_User_ConsumeHistoryController 控制器

对应表：user_account; 查询条件：user_id

select remain_times,used_times from `user_account` where `user_id` = 1 order by create_time

对应表：user_account_log_statistics 查询条件：user_id,time_index,data_key(这个字段暂时只有两种不同的值)

SELECT time_index,data_value FROM user_account_log_statistics WHERE user_id = 1 AND data_key = 'consumption' AND time_index > 20160808 AND  time_index < 20160816;

Bin_Credit_AccountLogStatistics 脚本

SELECT user_id,type,COUNT(0) AS data_value FROM user_account_log WHERE times =20160816 GROUP BY user_id,type

对应表：user_account_log; 查询条件：times, user_id, type;

Bin_Credit_AccountLogStatistics 脚本

INSERT INTO personal_credit.user_account_log_statistics (user_id, time_index, data_key , data_value, create_time) VALUES
  ('1','20160816','consumption','88',UNIX_TIMESTAMP(NOW())),
  ('1','20160816','csdkfksfs','88',UNIX_TIMESTAMP(NOW())),
  ('2','20160816','consumption','88',UNIX_TIMESTAMP(NOW())),
  ('2','20160816','csdkfksfs','88',UNIX_TIMESTAMP(NOW())),
  ('3','20160816','consumption','88',UNIX_TIMESTAMP(NOW()))ON DUPLICATE KEY UPDATE data_value = VALUES(data_value)

对应表：user_account_log_statistics; 查询条件：time_index,user_id,data_key,data_value;

Bin_Credit_ApiLogStatistics 脚本

SELECT user_id, api_id, status, count(0) FROM personal_credit.api_log WHERE create_time >= 1471190400 AND create_time < 1471276800 GROUP BY user_id, api_id, status WITH ROLLUP HAVING user_id IS NOT NULL AND api_id IS NOT NULL

对应表：api_log; 查询条件：create_time,user_id,api_id,status;

Bin_Credit_ApiLogStatistics 脚本

INSERT INTO personal_credit.api_log_statistics (time_index, user_id, api_id, data_key, data_value, create_time) VALUES (20160815, 1, 1,  'call_error', 2, UNIX_TIMESTAMP(NOW())),(20160815, 1, 1,  'call_success', 2, UNIX_TIMESTAMP(NOW())),(20160815, 1, 1,  'call_total', 4, UNIX_TIMESTAMP(NOW())),(20160815, 1, 3,  'call_success', 1, UNIX_TIMESTAMP(NOW())),(20160815, 1, 3,  'call_total', 1, UNIX_TIMESTAMP(NOW())),(20160815, 2, 1,  'call_success', 1, UNIX_TIMESTAMP(NOW())),(20160815, 2, 1,  'call_total', 1, UNIX_TIMESTAMP(NOW()))ON DUPLICATE KEY UPDATE data_value = VALUES(data_value)

对应表：api_log_statistics; 查询条件：time_index, user_id, api_id, data_key;

Credit_User_SearchHistoryController 控制器

SELECT * FROM `api_log_statistics` WHERE `api_id` IN (?) AND `data_key` = ? AND `time_index` <= ? AND `time_index` >= ? AND `user_id` = ? ORDER BY time_index ASC