개발새발

[2020.02.18] 주식이동평균, 전일대비, 등락률 본문

Framework/egovframework

[2020.02.18] 주식이동평균, 전일대비, 등락률

재래김유진 2020. 2. 18. 10:45
728x90
반응형

 

<주식 이동평균 쿼리>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
         select 
                item_id
                , deal_date
                , price_close
                , ROUND((CASE
                WHEN
                    5 = COUNT(*)
                    OVER (ORDER BY deal_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)
                THEN
                    AVG(SUM(price_close))
                    OVER (ORDER BY deal_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)
                END), 2) AS priceAvg5 //가격이동평균 5일
                , ROUND((CASE
                WHEN
                    10 = COUNT(*)
                    OVER (ORDER BY deal_date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW)
                THEN
                    AVG(SUM(price_close))
                    OVER (ORDER BY deal_date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW)
                END), 2) AS priceAvg10 //가격이동평균 10일
                , ROUND((CASE
                WHEN
                    20 = COUNT(*)
                    OVER (ORDER BY deal_date ROWS BETWEEN 19 PRECEDING AND CURRENT ROW)
                THEN
                    AVG(SUM(price_close))
                    OVER (ORDER BY deal_date ROWS BETWEEN 19 PRECEDING AND CURRENT ROW)
                END), 2) AS priceAvg20 //가격이동평균 20일
                , ROUND((CASE
                WHEN
                    60 = COUNT(*)
                    OVER (ORDER BY deal_date ROWS BETWEEN 59 PRECEDING AND CURRENT ROW)
                THEN
                    AVG(SUM(price_close))
                    OVER (ORDER BY deal_date ROWS BETWEEN 59 PRECEDING AND CURRENT ROW)
                END), 2) AS priceAvg60 //가격이동평균 60일
                , volume
                , ROUND((CASE
                WHEN
                    5 = COUNT(*)
                    OVER (ORDER BY deal_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)
                THEN
                    AVG(SUM(volume))
                    OVER (ORDER BY deal_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)
                END), 2) AS volumeAvg5 //거래량이동평균 5일
                , ROUND((CASE
                WHEN
                    20 = COUNT(*)
                    OVER (ORDER BY deal_date ROWS BETWEEN 19 PRECEDING AND CURRENT ROW)
                THEN
                    AVG(SUM(volume))
                    OVER (ORDER BY deal_date ROWS BETWEEN 19 PRECEDING AND CURRENT ROW)
                END), 2) AS volumeAvg20 //거래량이동평균 20일
                , ROUND((CASE
                WHEN
                    60 = COUNT(*)
                    OVER (ORDER BY deal_date ROWS BETWEEN 59 PRECEDING AND CURRENT ROW)
                THEN
                    AVG(SUM(volume))
                    OVER (ORDER BY deal_date ROWS BETWEEN 59 PRECEDING AND CURRENT ROW)
                END), 2) AS volumeAvg60 //거래량이동평균 60일
        FROM item_price
        WHERE item_id = #{itemId}
        
        AND date_format(deal_date,'%Y-%m-%d')
             BETWEEN date_format(DATE_ADD(#{dealDate},INTERVAL  -3 year), '%Y-%m-%d')
        AND date_format(#{dealDate},'%Y-%m-%d')
        
        group by deal_date, price_close
        order by deal_date DESC
        LIMIT 10
 
  

 

<ajax>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
function callAjax(itemId,dealDate) {
      
      $("#itemId").val(itemId);
      $("#dealDate").val(dealDate);
      
      $.ajax({
          url: "ajax/egovSampleAvg.do",
          data: {"itemId":itemId, "dealDate":dealDate},
          /* type: "post",  */
          dataType: "json",
          async: false,
          success:function(data) {
              /* alert(data+"에이작승이이이이잉");
              console.log(data);
              alert(itemId); */
              initAvg(data);
              $("#avgTable").removeClass("avgTable");
              
          },
          error: function a(e){
              alert("에러"+e);
              console.log(e);
      }});
  }
 
  

 

<ajax script data 뿌리기>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
function initAvg(obj){
    
    console.log("obj ==> ", obj);
    
    var html = ''; 
      
      for(var i = 0; i <obj['data'].length; i++){ 
          html += '<tr>'; 
          html += '<td class="listtd">'+obj.data[i].dealDate+'</td>'; 
          html += '<td class="listtd">'+obj.data[i].priceClose+'</td>'; 
          html += '<td class="listtd">'+obj.data[i].priceAvg5+'</td>'; 
          html += '<td class="listtd">'+obj.data[i].priceAvg10+'</td>'; 
          html += '<td class="listtd">'+obj.data[i].priceAvg20+'</td>'; 
          html += '<td class="listtd">'+obj.data[i].priceAvg60+'</td>'; 
          html += '<td class="listtd">'+obj.data[i].volume+'</td>'; 
          html += '<td class="listtd">'+obj.data[i].volumeAvg5+'</td>'; 
          html += '<td class="listtd">'+obj.data[i].volumeAvg20+'</td>'; 
          html += '<td class="listtd">'+obj.data[i].volumeAvg60+'</td>'; 
          html += '</tr>'; 
          }
      
      $("#dynamicTbody").empty(); 
      $("#dynamicTbody").append(html);
    
}
 
  

 

<html table 그리기>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
                <table width="100%" border="0" cellpadding="0" cellspacing="0" id="avgTable" class="avgTable">
                    <colgroup>
                        <col width="60"/>
                        <col width="60"/>
                        <col width="60"/>
                        <col width="80"/>
                        <col width="?"/>
                        <col width="60"/>
                    </colgroup>
                    <tr>
                        <!-- <th align="center">No</th> -->
                        <th align="center" rowspan="2"><spring:message code="title.sample.deal_date" /></th>
                        <th align="center" rowspan="2"><spring:message code="title.sample.price_close" /></th>
                        <th align="center" colspan="4"><spring:message code="title.price.avg" /></th>
                        <th align="center" rowspan="2"><spring:message code="title.sample.volume" /></th>
                        <th align="center" colspan="4"><spring:message code="title.volume.avg" /></th>
                    </tr>
                    <tr>
                        <!-- <th align="center">No</th> -->
                        
                        <th align="center"><spring:message code="title.price.5" /></th>
                        <th align="center"><spring:message code="title.price.10" /></th>
                        <th align="center"><spring:message code="title.price.20" /></th>
                        <th align="center"><spring:message code="title.price.60" /></th>
                        <th align="center"><spring:message code="title.price.5" /></th>
                        <th align="center"><spring:message code="title.price.20" /></th>
                        <th align="center"><spring:message code="title.price.60" /></th>
                    </tr>
                    <tbody id="dynamicTbody">
                    
                    </tbody>
                </table>
 
  


        

<ajax controller>

1
2
3
4
5
6
7
8
9
10
11
12
13
@RequestMapping("ajax/egovSampleAvg.do")
    @ResponseBody
    public Map<String,Object>  updateSampleView1( String itemId, SampleDefaultVO searchVO, Model model, SampleVO sampleVO) throws Exception {
        
        System.out.println("평균파미아이디"+itemId);
        Map<String, Object> rtnMap = new HashMap<String, Object>();
        List<SampleVO> sampleavg = sampleService.selectSampleAvg(sampleVO);
        
        System.out.println("평균~~~"+sampleavg);
        rtnMap.put("data", sampleavg);
        
        return rtnMap;
    }
 
 http://colorscripter.com/info#e" target="_blank" style="text-decoration:none;color:white">cs

 

요건 찐이야,, 

 

 

 

<전일대비, 등락률 쿼리 & 검색>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
<select id="selectSampleList" parameterType="searchVO"  resultMap="sample">
            SELECT item_id,
               item_name,
               deal_date,
               price_open,
               price_high,
               price_low,
               price_close,
               volume,
              PPC, //전일종가
              PD, //전일대비
              ROUND(((price_close-PPC) / PPC *100),2) as FR  //등락률
                 FROM( SELECT 
                       A.item_id,
                       A.item_name,
                       B.deal_date,
                       B.price_open,
                       B.price_high,
                       B.price_low,
                       B.price_close,
                       B.volume,
                       lag(B.price_close,1) over(partition by A.item_id order by B.deal_date) as PPC,
                         B.price_close-lag(B.price_close,1) over(partition by A.item_id order by B.deal_date) as PD
                    FROM 
                        item A
                    INNER JOIN 
                        item_price B
                    ON 
                        A.item_id = B.item_id
                ) T1
                WHERE 1=1
                    <choose>
                        <when test="searchRegDate != null and searchRegDate !=''">
                            AND    deal_date LIKE '%' #{searchRegDate} '%'
                        </when>
                        <otherwise>
                          AND   deal_date = '20200131'
                        </otherwise>
                    </choose>
                    <choose>
                        <when test='searchCode != null and searchCode != ""'>
                            AND (item_id LIKE '%' #{searchCode} '%' OR item_name LIKE '%' #{searchCode} '%')
                        </when>
                    </choose>
                    ORDER BY deal_date, item_id ASC
                    LIMIT #{recordCountPerPage} OFFSET #{firstIndex} 
    </select>
 
  

 

<controller>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
@RequestMapping(value = "/egovSampleList.do")
    public String selectSampleList(@ModelAttribute("searchVO") SampleDefaultVO searchVO,  ModelMap model, SampleVO sampleVO) throws Exception {
 
        /** EgovPropertyService.sample */
        searchVO.setPageUnit(propertiesService.getInt("pageUnit"));
        searchVO.setPageSize(propertiesService.getInt("pageSize"));
 
        /** pageing setting */
        PaginationInfo paginationInfo = new PaginationInfo();
        paginationInfo.setCurrentPageNo(searchVO.getPageIndex());
        paginationInfo.setRecordCountPerPage(searchVO.getPageUnit());
        paginationInfo.setPageSize(searchVO.getPageSize());
 
        searchVO.setFirstIndex(paginationInfo.getFirstRecordIndex());
        searchVO.setLastIndex(paginationInfo.getLastRecordIndex());
        searchVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage());
 
        List<SampleVO> sampleList = sampleService.selectSampleList(searchVO);
        
        model.addAttribute("resultList", sampleList);
        int totCnt = sampleService.selectSampleListTotCnt(searchVO);
        paginationInfo.setTotalRecordCount(totCnt);
        model.addAttribute("paginationInfo", paginationInfo);
        
        model.addAttribute("totCnt", totCnt);
 
        return "sample/egovSampleList";
    }
 
  

 

 

<html table 그리기>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
            <div id="table">
                <table width="100%" border="0" cellpadding="0" cellspacing="0" summary="카테고리ID, 케테고리명, 사용여부, Description, 등록자 표시하는 테이블">
                    <caption style="visibility:hidden">카테고리ID, 케테고리명, 사용여부, Description, 등록자 표시하는 테이블</caption>
                    <colgroup>
                        <col width="60"/>
                        <col width="110"/>
                        <col width="100"/>
                        <col width="80"/>
                        <col width="?"/>
                        <col width="60"/>
                    </colgroup>
                    <tr>
                        <!-- <th align="center">No</th> -->
                        <th align="center"><spring:message code="title.sample.item_id" /></th>
                        <th align="center"><spring:message code="title.sample.item_name" /></th>
                        <th align="center"><spring:message code="title.sample.price_open" /></th>
                        <th align="center"><spring:message code="title.sample.price_high" /></th>
                        <th align="center"><spring:message code="title.sample.price_low" /></th>
                        <th align="center"><spring:message code="title.sample.price_close" /></th>
                        <th align="center"><spring:message code="title.sample.price_yester" /></th>
                        <th align="center"><spring:message code="title.sample.price_updown" /></th>
                        <th align="center"><spring:message code="title.sample.volume" /></th>
                    </tr>
                    <c:forEach var="result" items="${resultList}" varStatus="status">
                        <tr>
                            <%-- <td align="center" class="listtd"><c:out value="${paginationInfo.totalRecordCount+1 - ((searchVO.pageIndex-1) * searchVO.pageSize + status.count)}"/></td> --%>
                            <td align="center" class="listtd" ><c:out value="${result.itemId}" /></td>
                            <td align="left" class="listtd" ><a href="javascript:fn_egov_select('<c:out value="${result.itemId}"/>','<c:out value="${result.dealDate}"/>')"><c:out value="${result.itemName}" />&nbsp;</a></td>
                            <td align="center" class="listtd" ><fmt:formatNumber type="number" value="${result.priceOpen}" pattern="#,###"/>&nbsp;</td>
                            <td align="center" class="listtd"><fmt:formatNumber type="number" value="${result.priceHigh}" pattern="#,###"/>&nbsp;</td>
                            <td align="center" class="listtd" ><fmt:formatNumber type="number" value="${result.priceLow}" pattern="#,###"/>&nbsp;</td>
                            <td align="center" class="listtd"><fmt:formatNumber type="number" value="${result.priceClose}" pattern="#,###"/>&nbsp;</td>
                            <c:if test="${result.PD > 0}">
                                <td align="center" class="listtd" style="color:red;"><fmt:formatNumber type="number" value="${result.PD}" pattern="▲#,###"/>&nbsp;</td>
                            </c:if>
                            <c:if test="${result.PD < 0}">
                                <td align="center" class="listtd" style="color:blue;">▼<fmt:formatNumber type="number" value="${result.PD}" pattern="▲#,###; #,###"/>&nbsp;</td>
                            </c:if>
                            <c:if test="${result.PD == 0}">
                                <td align="center" class="listtd"><c:out value="${result.PD}"/>&nbsp;</td>
                            </c:if>
                            <c:if test="${result.FR > 0}">
                                <td align="center" class="listtd" style="color:red;">+<c:out value="${result.FR}"/>&nbsp;</td>
                            </c:if>
                            <c:if test="${result.FR < 0}">
                                <td align="center" class="listtd" style="color:blue;"><c:out value="${result.FR}"/>&nbsp;</td>
                            </c:if>
                            <c:if test="${result.FR == 0}">
                                <td align="center" class="listtd"><c:out value="0"/>&nbsp;</td>
                            </c:if>
                            <td align="center" class="listtd"><fmt:formatNumber type="number" value="${result.volume}" pattern="#,###"/>&nbsp;</td>
                        </tr>
                    </c:forEach>
                </table>
            </div>
 
  

 

 

뀨?

 

728x90
반응형

'Framework > egovframework' 카테고리의 다른 글

[egovFrameWork] 한글검색안됨  (0) 2020.02.12
[2020.01.30] Excel upload 후 db저장  (23) 2020.01.30
[egovFrameWork] mybatis & ibatis  (2) 2020.01.20
'Framework/egovframework' Related Articles more
Comments