def api_getSndDB(apiObj, stockCode, date):
apiObj.set_input_value("일자", date)
apiObj.set_input_value("종목코드", stockCode)
apiObj.set_input_value("금액수량구분", '2')
apiObj.set_input_value("매매구분", '0')
apiObj.set_input_value("단위구분", '1')
apiObj.comm_rq_data("opt10060_req", "opt10060", 0, "0796")
query = '''
SELECT DATE_FORMAT(DATE,"%%Y%%m%%d") AS DATE
FROM jazzdb.T_STOCK_SND_DAY
WHERE 1=1
AND STOCKCODE = '%s'
''' % (stockCode)
datelist = db.selectSingleColumn(query)
data = []
for eachLine in readTempFile():
if eachLine[0] not in datelist:
data.append(tuple([stockCode] + eachLine))
insertQuery = '''INSERT INTO jazzdb.T_STOCK_SND_DAY
VALUES ''' + str(data)[1:-1]
db.insert(insertQuery)
return len(data)
def api_getSndForWin(apiObj, stockCode, date, winCode):
apiObj.set_input_value("회원사코드", winCode)
apiObj.set_input_value("종목코드", stockCode)
apiObj.set_input_value("종료일자", date)
apiObj.comm_rq_data("opt10078_req", "opt10078", 0, "0127")
query = '''
SELECT DATE_FORMAT(DATE,"%%Y%%m%%d") AS DATE
FROM jazzdb.T_STOCK_SND_WINDOW_ISOLATED
WHERE 1=1
AND STOCKCODE = '%s' AND WINCODE = '%s'
''' % (stockCode, winCode)
datelist = db.selectSingleColumn(query)
data = []
# tempStr = tempStr + date + '\t' + price + '\t' + volume + '\t' + forSum + '\t' + insSum + '\t' + per + '\t' + finan + '\t' + samo + '\t' + yg + '\t' + tusin + '\t' + insur + '\t' + nation + '\t' + bank + '\t' + otherfinan + '\t' + othercorpor + '\t' + otherfor + '\n'
for eachLine in readTempFile():
if eachLine[0] not in datelist:
data.append(tuple([stockCode,winCode] + eachLine))
insertQuery = '''INSERT INTO jazzdb.T_STOCK_SND_WINDOW_ISOLATED
VALUES ''' + str(data)[1:-1]
print(insertQuery)
db.insert(insertQuery)
return len(data)
def api_sendorder(apiObj, stockcode, action, close, vol, debug=False):
s_rqname = 'gyb0418'
s_screenno = '0101'
s_accno = '5288885510'
if (action == 'S'):
s_ordertype = 2
else:
s_ordertype = 1
s_code = stockcode
s_quantity = int(vol)
s_price = int(close)
s_hoga = '00'
s_orderno = ''
# def send_order(self, rqname, screen_no, acc_no, order_type, code, quantity, price, hoga, order_no):
status = apiObj.send_order(s_rqname, s_screenno, s_accno, s_ordertype,
s_code, s_quantity, s_price, s_hoga, s_orderno)
q = '''
INSERT INTO `jazzdb`.`T_TRADING_HISTORY_TICK` (`STOCKCODE`, `DATE`, `TIME`, `ACTION`, `PRICE`, `AMOUNT`, `DONE`, `ORDERTYPE`) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s');
''' % (s_code, datetime.now().date(), datetime.now().time(), s_ordertype,
s_price, s_quantity, '0', '0')
print(q)
db.insert(q)
return status
def api_getDayChart(apiObj, stockCode, date):
apiObj.set_input_value("종목코드", stockCode)
apiObj.set_input_value("기준일자", date)
apiObj.set_input_value("수정주가구분", 1)
apiObj.comm_rq_data("opt10081_req", "opt10081", 0, "0101")
query = '''
SELECT DATE_FORMAT(DATE,"%%Y%%m%%d") AS DATE
FROM jazzdb.T_STOCK_OHLC_DAY
WHERE 1=1
AND STOCKCODE = '%s'
''' % (stockCode)
datelist = db.selectSingleColumn(query)
data = []
# tempStr = tempStr + stockCode + '\t' + date + '\t' + open + '\t' + high + '\t' + low + '\t' + close + '\t' + value + '\t' + str(adjustClass) + '\t' + str(adjustRatio) + '\n'
for eachLine in readTempFile():
if eachLine[1] not in datelist:
data.append(tuple(eachLine))
insertQuery = '''
INSERT INTO jazzdb.T_STOCK_OHLC_DAY
VALUES ''' + str(data)[1:-1]
db.insert(insertQuery)
return len(data)
def get_stake_info(code):
url = "http://companyinfo.stock.naver.com/v1/company/c1010001.aspx?cmp_cd=%s&target=finsum_more" % (
code)
html = requests.get(url).text
df_list = pd.read_html(html) #, index_col='주요재무정보')
distriPerc = df_list[1].ix[6][1].split('/')[1]
shares = df_list[1].ix[6][1].split('/')[0]
print(["발행주식수", int(shares.replace('주 ', '').replace(',', ''))])
print([
'유통주식수',
int(
int(shares.replace('주 ', '').replace(',', '')) *
float(distriPerc.replace('%', '')) * 0.01)
])
for i in range(0, len(df_list[4])):
if (str(df_list[4].ix[i][0]) != 'nan'):
print([
df_list[4].ix[i][0].split('외')[0].strip(),
int(round(df_list[4].ix[i][1]))
])
obj = []
obj.append(["발행주식수", int(shares.replace('주 ', '').replace(',', ''))])
obj.append([
'유통주식수',
int(
int(shares.replace('주 ', '').replace(',', '')) *
float(distriPerc.replace('%', '')) * 0.01)
])
for i in range(0, len(df_list[4])):
if (str(df_list[4].ix[i][0]) != 'nan'):
obj.append([
df_list[4].ix[i][0].split('외')[0].strip(), df_list[4].ix[i][1]
])
for eachObj in obj:
#데이터 db insert
query = 'INSERT INTO jazzdb.T_STOCK_SHARES_INFO VALUES("%s","%s","%s","%s")' % (
code, eachObj[0], eachObj[1], dp.todayStr('n'))
db.insert(query)
import manager.dbConnector as db queryt = ''' TRUNCATE jazzdb.T_DATE_INDEXED; ''' querye = ''' INSERT INTO jazzdb.T_DATE_INDEXED ( SELECT A.DATE, @ROWNUM := @ROWNUM+1 AS CNT FROM ( SELECT DATE FROM jazzdb.T_STOCK_SND_DAY GROUP BY DATE ORDER BY DATE DESC ) A , (SELECT @ROWNUM := -1) R ); ''' db.insert(queryt) db.insert(querye)
def _receive_chejan_data(self, gubun, item_cnt, fid_list):
'''
FID 설명
https://download.kiwoom.com/web/openapi/kiwoom_openapi_plus_devguide_ver_1.1.pdf
@ 9201 계좌번호
@ 9001 종목코드
@ 9203 주문번호
@ 900 주문수량
@ 901 주문가격
@ 904 원주문번호
@ 908 주문/체결시간 HHMMSSMS
@ 909 체결번호
@ 910 체결가
@ 911 체결량
'''
# 주문메타
acc = self.get_chejan_data(9201).strip()
stockcode = self.get_chejan_data(9001).strip().replace('A','')
o_time = self.get_chejan_data(908) # 115002
o_date = datetime.now().date()
# 주문정보
o_no = self.get_chejan_data(9203)
o_type_detail = self.get_chejan_data(905) # +매수 / +매수정정 / -매도/ -매도정정
o_type = self.get_chejan_data(907) # 1 매도 / 2 매수
o_state = self.get_chejan_data(913) # 접수 / 확인 / 체결
o_amount = self.get_chejan_data(900)
o_price = self.get_chejan_data(901)
o_origin = self.get_chejan_data(904)
o_datetime = self.get_chejan_data(908) # 115002
# 체결정보
c_no = self.get_chejan_data(909)
c_price = self.get_chejan_data(910)
c_amount = self.get_chejan_data(911)
# '''
# CREATE TABLE `jazztrade`.`T_TRADE_ORDER` (
# `ACC` VARCHAR(11) NOT NULL,
# `DATE` DATE NOT NULL,
# `TIME` TIME NOT NULL,
# `STOCKCODE` VARCHAR(11) NOT NULL,
# `O_NO` VARCHAR(11) NOT NULL,
# `O_STATE` VARCHAR(1) NOT NULL,
# `O_TYPE` VARCHAR(11) NOT NULL,
# `O_TYPE_DET` VARCHAR(11) NOT NULL,
# `O_PRICE` INT NOT NULL,
# `O_AMOUNT` INT NOT NULL,
# `O_ORIGIN` VARCHAR(11) NOT NULL,
# `C_NO` VARCHAR(11) NULL,
# `C_PRICE` INT NULL,
# `C_AMOUNT` INT NULL,
# PRIMARY KEY (`ACC`, `DATE`, `TIME`, `STOCKCODE`, `O_NO`, `O_TYPE`));
#
# '''
# 매수 / 매도 주문
global idx
if o_state == '접수':
q = '''
INSERT INTO `jazztrade`.`T_TRADE_ORDER_PROD`
(`ACC`, `DATE`, `TIME`, `STOCKCODE`, `O_NO`, `O_STATE`, `O_TYPE`, `O_TYPE_DET`, `O_PRICE`, `O_AMOUNT`, `O_ORIGIN`, `T_DEBUG`)
VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', %s, %s, '%s', %s);
''' % (
acc, o_date, o_time, stockcode, o_no, o_state, o_type, o_type_detail, o_price, o_amount, o_origin, idx)
print(o_state, q)
db.insert(q)
idx += 1
elif o_state == '체결':
# 매수 / 매도 체결
q = '''
INSERT INTO `jazztrade`.`T_TRADE_ORDER_PROD`
(`ACC`, `DATE`, `TIME`, `STOCKCODE`, `O_NO`, `O_STATE`, `O_TYPE`, `O_TYPE_DET`, `O_PRICE`, `O_AMOUNT`, `O_ORIGIN`, `C_NO`, `C_PRICE`, `C_AMOUNT`, `T_DEBUG`)
VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', %s, %s, '%s', '%s', %s, %s, %s);
''' % (
acc, o_date, o_time, stockcode, o_no, o_state, o_type, o_type_detail, o_price, o_amount, o_origin, c_no,
c_price, c_amount, idx)
print(o_state, q)
idx += 1
db.insert(q)
elif o_state == '확인':
# 매수 / 매도 정정
q = '''
INSERT INTO `jazztrade`.`T_TRADE_ORDER_PROD`
(`ACC`, `DATE`, `TIME`, `STOCKCODE`, `O_NO`, `O_STATE`, `O_TYPE`, `O_TYPE_DET`, `O_PRICE`, `O_AMOUNT`, `O_ORIGIN`, `T_DEBUG`)
VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', %s, %s, '%s', %s);
''' % (
acc, o_date, o_time, stockcode, o_no, o_state, o_type, o_type_detail, o_price, o_amount, o_origin, idx)
print(o_state, q)
idx += 1
db.insert(q)
'select stockcode, stockname from jazzdb.T_STOCK_CODE_MGMT')
codeDic = {} # code : name
# jazzdb.t_stock_code_mgmt 테이블
# 신규상장종목 및 종목명 변경여부 확인,업데이트하는 소스
# 데이터 출처 : Kiwoom
for each in old_list:
codeDic[each[0]] = each[1]
new_list = apiObj.get_list('p')
new_list = new_list + apiObj.get_list('d')
for eachcode in new_list:
# 신규 상장 종목 인서트
if eachcode[0] != '' and eachcode[0] not in codeDic.keys():
print(eachcode + [dm.todayStr('-')])
db.insert(
"INSERT INTO `jazzdb`.`T_STOCK_CODE_MGMT` (`STOCKCODE`, `STOCKNAME`, `MARKET`, `LISTED`, `UPDATEDATE`) VALUES ('%s', '%s', '%s', '%s','%s');"
% tuple(eachcode + [dm.todayStr('-')]))
print("신규상장 종목", eachcode)
# 종목명 바뀐경우 업데이트
if eachcode[0] != '' and eachcode[0] in codeDic.keys(
) and eachcode[1] != codeDic[eachcode[0]]:
print("종목명 변경", codeDic[eachcode[0]], eachcode)
db.insert(
"UPDATE `jazzdb`.`T_STOCK_CODE_MGMT` SET `STOCKNAME` = '%s', `UPDATEDATE` = '%s' WHERE (`STOCKCODE` = '%s')"
% (eachcode[1], dm.todayStr('-'), eachcode[0]))
apiObj.destroy()
def analysisSndBasicEachDay(stockcode, date):
query = '''
INSERT INTO jazzdb.T_STOCK_SND_ANALYSIS_RESULT_TEMP
SELECT RS.STOCKCODE, RS.DATE, RS.CLOSE
,ROUND((CLOSE-C2)/C2,5) AS P1
,ROUND((CLOSE-C3)/C3,5) AS P3
,ROUND((CLOSE-C5)/C5,5) AS P5
,ROUND((CLOSE-C20)/C20,5) AS P20
,ROUND((CLOSE-C60)/C60,5) AS P60
,ROUND(I1/SHARE,5) AS I1
,ROUND(I3/SHARE,5) AS I3
,ROUND(I5/SHARE,5) AS I5
,ROUND(I20/SHARE,5) AS I20
,ROUND(I60/SHARE,5) AS I60
,ROUND(F1/SHARE,5) AS I1
,ROUND(F3/SHARE,5) AS I3
,ROUND(F5/SHARE,5) AS I5
,ROUND(F20/SHARE,5) AS I20
,ROUND(F60/SHARE,5) AS I60
,ROUND(PS1/SHARE,5) AS PS1
,ROUND(PS3/SHARE,5) AS PS3
,ROUND(PS5/SHARE,5) AS PS5
,ROUND(PS20/SHARE,5) AS PS20
,ROUND(PS60/SHARE,5) AS PS60
,ROUND(FN1/SHARE,5) AS FN1
,ROUND(FN3/SHARE,5) AS FN3
,ROUND(FN5/SHARE,5) AS FN5
,ROUND(FN20/SHARE,5) AS FN20
,ROUND(FN60/SHARE,5) AS FN60
,ROUND(YG1/SHARE,5) AS YG1
,ROUND(YG3/SHARE,5) AS YG3
,ROUND(YG5/SHARE,5) AS YG5
,ROUND(YG20/SHARE,5) AS YG20
,ROUND(YG60/SHARE,5) AS YG60
,ROUND(S1/SHARE,5) AS S1
,ROUND(S3/SHARE,5) AS S3
,ROUND(S5/SHARE,5) AS S5
,ROUND(S20/SHARE,5) AS S20
,ROUND(S60/SHARE,5) AS S60
,ROUND(T1/SHARE,5) AS T1
,ROUND(T3/SHARE,5) AS T3
,ROUND(T5/SHARE,5) AS T5
,ROUND(T20/SHARE,5) AS T20
,ROUND(T60/SHARE,5) AS T60
,ROUND(IS1/SHARE,5) AS IS1
,ROUND(IS3/SHARE,5) AS IS3
,ROUND(IS5/SHARE,5) AS IS5
,ROUND(IS20/SHARE,5) AS IS20
,ROUND(IS60/SHARE,5) AS IS60
,ROUND(BK1/SHARE,5) AS BK1
,ROUND(BK3/SHARE,5) AS BK3
,ROUND(BK5/SHARE,5) AS BK5
,ROUND(BK20/SHARE,5) AS BK20
,ROUND(BK60/SHARE,5) AS BK60
,ROUND(NT1/SHARE,5) AS NT1
,ROUND(NT3/SHARE,5) AS NT3
,ROUND(NT5/SHARE,5) AS NT5
,ROUND(NT20/SHARE,5) AS NT20
,ROUND(NT60/SHARE,5) AS NT60
,ROUND(OC1/SHARE,5) AS OC1
,ROUND(OC3/SHARE,5) AS OC3
,ROUND(OC5/SHARE,5) AS OC5
,ROUND(OC20/SHARE,5) AS OC20
,ROUND(OC60/SHARE,5) AS OC60
FROM
(
SELECT A.STOCKCODE, A.DATE, ABS(A.CLOSE) AS CLOSE, C.SHARE+0.01 AS SHARE
, SUM(ABS(A.CLOSE)) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS C2
, SUM(ABS(A.CLOSE)) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 3 PRECEDING AND 3 PRECEDING) AS C3
, SUM(ABS(A.CLOSE)) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 5 PRECEDING AND 5 PRECEDING) AS C5
, SUM(ABS(A.CLOSE)) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 20 PRECEDING AND 20 PRECEDING) AS C20
, SUM(ABS(A.CLOSE)) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 60 PRECEDING AND 60 PRECEDING) AS C60
# 3,5,10,20 누적 매수
, A.INS AS I1
, SUM(A.INS) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS I3
, SUM(A.INS) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS I5
#, SUM(A.INS) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS I10
, SUM(A.INS) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS I20
, SUM(A.INS) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 59 PRECEDING AND CURRENT ROW) AS I60
, A.FOREI AS F1
, SUM(A.FOREI) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS F3
, SUM(A.FOREI) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS F5
#, SUM(A.FOREI) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS F10
, SUM(A.FOREI) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS F20
, SUM(A.FOREI) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 59 PRECEDING AND CURRENT ROW) AS F60
, A.PER AS PS1
, SUM(A.PER) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS PS3
, SUM(A.PER) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS PS5
#, SUM(A.PER) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS PS10
, SUM(A.PER) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS PS20
, SUM(A.PER) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 59 PRECEDING AND CURRENT ROW) AS PS60
, A.FINAN AS FN1
, SUM(A.FINAN) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS FN3
, SUM(A.FINAN) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS FN5
#, SUM(A.FINAN) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS FN10
, SUM(A.FINAN) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS FN20
, SUM(A.FINAN) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 59 PRECEDING AND CURRENT ROW) AS FN60
, A.YG AS YG1
, SUM(A.YG) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS YG3
, SUM(A.YG) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS YG5
#, SUM(A.YG) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS YG10
, SUM(A.YG) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS YG20
, SUM(A.YG) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 59 PRECEDING AND CURRENT ROW) AS YG60
, A.SAMO AS S1
, SUM(A.SAMO) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS S3
, SUM(A.SAMO) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS S5
#, SUM(A.SAMO) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS S10
, SUM(A.SAMO) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS S20
, SUM(A.SAMO) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 59 PRECEDING AND CURRENT ROW) AS S60
, A.TUSIN AS T1
, SUM(A.TUSIN) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS T3
, SUM(A.TUSIN) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS T5
#, SUM(A.TUSIN) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS T10
, SUM(A.TUSIN) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS T20
, SUM(A.TUSIN) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 59 PRECEDING AND CURRENT ROW) AS T60
, A.INSUR AS IS1
, SUM(A.INSUR) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS IS3
, SUM(A.INSUR) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS IS5
#, SUM(A.INSUR) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS IS10
, SUM(A.INSUR) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS IS20
, SUM(A.INSUR) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 59 PRECEDING AND CURRENT ROW) AS IS60
, A.NATION AS NT1
, SUM(A.NATION) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS NT3
, SUM(A.NATION) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS NT5
#, SUM(A.NATION) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS NT10
, SUM(A.NATION) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS NT20
, SUM(A.NATION) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 59 PRECEDING AND CURRENT ROW) AS NT60
, A.BANK AS BK1
, SUM(A.BANK) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS BK3
, SUM(A.BANK) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS BK5
#, SUM(A.BANK) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS BK10
, SUM(A.BANK) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS BK20
, SUM(A.BANK) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 59 PRECEDING AND CURRENT ROW) AS BK60
, A.OTHERCORPOR AS OC1
, SUM(A.OTHERCORPOR) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS OC3
, SUM(A.OTHERCORPOR) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS OC5
#, SUM(A.OTHERCORPOR) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS OC10
, SUM(A.OTHERCORPOR) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS OC20
, SUM(A.OTHERCORPOR) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 59 PRECEDING AND CURRENT ROW) AS OC60
FROM jazzdb.T_STOCK_SND_DAY A
JOIN jazzdb.T_DATE_INDEXED B USING (DATE)
JOIN (
SELECT STOCKCODE, SHARE
FROM
(
SELECT STOCKCODE, SHARE, ROW_NUMBER() OVER (PARTITION BY STOCKCODE ORDER BY DATE DESC) AS RN
FROM jazzdb.T_STOCK_SHARES_INFO
WHERE 1=1
AND HOLDER = '유통주식수'
) T1
WHERE 1=1
AND T1.RN = 1
) C ON (A.STOCKCODE = C.STOCKCODE)
WHERE 1=1
AND A.STOCKCODE = '%s'
AND B.CNT < 70
) RS
WHERE RS.DATE = '%s'
;
''' % (stockcode, date)
db.insert(query)
