데이터베이스 질문과 답변
달력별 통계를 이어 붙히고 싶습니다..
0
2
888
by Tension [2016.03.22 11:55:54]
select Date_format(date,'%Y-%m-%d') as date , rank
from( select a.dt as date, round(COUNT(B.RANK)/10*100,2) as rank from (
SELECT dt + INTERVAL lv-1 DAY dt
FROM (
SELECT ordinal_position lv
, CONCAT('201601', '01') dt //변수(201601)
FROM information_schema.columns
WHERE table_schema = 'mysql'
AND table_name = 'user'
) a
WHERE lv <= DAY(LAST_DAY(dt))
) a left join (
select a.rank as rank,DATE_FORMAT(rank_date, '%Y-%m-%d') as rank_date,b.info_goalrank from keyword_record as a join client_collect_info as b
where a.client_collect_seq = b.client_collect_seq
and a.client_collect_seq =544 // ( 544 변수)
and hour(a.rank_date) >=9
and hour(a.rank_date) <=18
and a.rank <=b.info_goalrank
and a.rank !=0
) b
on a.dt = b.rank_date
GROUP BY A.DT) as ex
결과값
| 2016-03-01 | 100.00 |
| 2016-03-02 | 100.00 |
| 2016-03-03 | 90.00 |
| 2016-03-04 | 40.00 |
| 2016-03-05 | 90.00 |
| 2016-03-06 | 0.00 |
| 2016-03-07 | .. |
| 2016-03-08 | .. |
| 2016-03-09 | .. |
| 2016-03-10 | .. |
| 2016-03-11 | .. |
| .. | .. |
| .. | .. |
| .. | .. |
| . | .. |
| . | .. |
| . | |
| . | .. |
| . | |
| . | |
| . | |
| 2016-03-31 |
이런 결과가 나오는 쿼리문이 잇습니다..
그런데 client_collect_seq 를 여러번 불러와야 할때는 컨트롤러 에서
for(seq 갯수만큼){
service.쿼리문(seq);
}
이렇게 불러오다 보니 속도에 문제점이 많습니다
혹시 개선하여
| date | seq1_rank | seq1_rank | .. |
| 2016-01-01 | 100.00 | 80.00 | .. |
| .. | .. | .. | |
| .. | .. | .. | |
| 2016-01-31 | 90.00 | 10.00 | .. |
이런식으로 컬림이 늘어나게 만들수는 없을까요?
조언 부탁드립니다 (__)
by 마농
[2016.03.22 13:36:43]
SELECT a.dt
, IFNULL(b.seq1_rank, 0) seq1_rank
, IFNULL(b.seq2_rank, 0) seq2_rank
, IFNULL(b.seq3_rank, 0) seq3_rank
, IFNULL(b.seq4_rank, 0) seq4_rank
FROM (SELECT DATE_FORMAT(dt + INTERVAL ordinal_position-1 DAY, '%Y-%m-%d') dt
FROM information_schema.columns
JOIN (SELECT CONCAT('201601', '01') dt) a
ON ordinal_position <= DAY(LAST_DAY(dt))
WHERE table_schema = 'mysql'
AND table_name = 'user'
) a
LEFT OUTER JOIN
(SELECT DATE_FORMAT(a.rank_date, '%Y-%m-%d') AS dt
, COUNT(CASE a.client_collect_seq WHEN 544 THEN 1 END) * 10 seq1_rank
, COUNT(CASE a.client_collect_seq WHEN 545 THEN 1 END) * 10 seq2_rank
, COUNT(CASE a.client_collect_seq WHEN 546 THEN 1 END) * 10 seq3_rank
, COUNT(CASE a.client_collect_seq WHEN 547 THEN 1 END) * 10 seq4_rank
FROM keyword_record AS a
JOIN client_collect_info AS b
ON a.client_collect_seq = b.client_collect_seq
AND a.rank <= b.info_goalrank
WHERE HOUR(a.rank_date) >= 9
AND HOUR(a.rank_date) <= 18
AND a.rank != 0
AND DATE_FORMAT(a.rank_date, '%Y%m') = '201601'
GROUP BY DATE_FORMAT(a.rank_date, '%Y-%m-%d')
) b
ON a.dt = b.dt
ORDER BY a.dt
;
by Tension
[2016.03.22 14:05:59]
와... 허허 감사합니다.. 마농님께 또하나 배워갑니다 (__)
) 버튼을 클릭하여 작성 하시면 됩니다.