<!DOCTYPE html>
<html lang="en" dir="ltr">
<head>
<meta charset="utf-8">
<title></title>
</head>
<body>
<input type="text" placeholder="num1을 입력" id="num1">
<input type="text" placeholder="num2를 입력" id="num2">
<input type="text" placeholder="num3를 입력" id="num3">
<input type="button" value="가장 큰 수는?" id="Bnt"></button>
<br><br>
가장 큰 수는 : <span id="result"></span>
</body>
<script src="http://code.jquery.com/jquery-latest.min.js"></script>
<script>
// 자바스크립트 함수 사용법
function functionName1() {
console.log(1);
}
var functionName2 = function() {
console.log(2);
}
var result1 = functionName1();
var result2 = functionName2();
console.log("----------------------");
// for문 예제
var cnt = 0;
for ( ; ; ) {
cnt++;
console.log(cnt);
if(cnt>5) break;
}
console.log("----------------------");
// 숫자 3개 입력 받아 가장 큰 수 구하기
$('#Bnt').click(function () {
var response = findLargeNumber();
$('#result').html(response);
})
function findLargeNumber(num1, num2, num3) {
var num1 = parseInt($('#num1').val());
var num2 = parseInt($('#num2').val());
var num3 = parseInt($('#num3').val());
if (num1 == num2 == num3) {
return num1
} else if (num1 > num2) {
if (num1 > num3) {
return num1
} else {
return num3
}
} else {
if (num2 > num3) {
return num2
} else {
return num3
}
}
}
</script>
</html>
가장 큰 수 구하기 좀 더 좋은 버전
var express = require('express');
var app = express();
var http = require('http');
var server = http.createServer(app).listen(80);
console.log("server is running...")
// 1. 가장 큰 수 구하기 함수
app.get('/function', function (req, res) {
res.sendfile("function.html");
});
var testVal = "abc";
app.get('/setVal', function (req, res) {
testVal = req.query.val;
res.send("ok");
});
app.get('/getVal', function(req, res) {
res.send(testVal)
});
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title></title>
</head>
<body>
<input type="text" name="num1">
<input type="text" name="num2">
<input type="text" name="num3">
<input type="button" id="compareBtn" value="버튼">
<br>
가장 큰 수는 : <span id="result"></span>
</body>
<script src="http://code.jquery.com/jquery-latest.min.js"></script>
<script>
$("#compareBtn").click(function(){
var maxVal = getMax();
console.log(maxVal);
$("#result").html(maxVal);
});
function getMax(val1, val2, val3) {
var val1 = $("input[name=num1]").val();
var val2 = $("input[name=num2]").val();
var val3 = $("input[name=num3]").val();
var maxVal = val1;
console.log(val1);
console.log(val2);
console.log(val3);
// console.log(maxVal);
if (maxVal < val2) {
maxVal = val2;
// console.log(maxVal);
}
if (maxVal < val3) {
maxVal = val3;
// console.log(maxVal);
}
return maxVal;
}
</script>
</html>
-- 1. 다음 중 사용자의 입력값을 받기 위해 사용하는 특수문자는?
-- *, $, &, @ 답 : &
-- 2. 다음 중 YEARWEEK 오름차순, PRODUCT 내림차순으로 정렬하는 방법 중 옳은 것은?
-- ORDER BY YEARWEEK ASC, PRODUCT DESC
-- 3. 112 서버 내 "SORT_EXAMPLE"테이블을 WEEK 별로 내림차순정렬하는 쿼리문을 작성하세요.
SELECT * FROM SORT_EXAMPLE
WHERE 1=1
ORDER BY TO_NUMBER(WEEK) DESC;
-- 4. 번문제 (kopo_channel_result)
SELECT PRODUCTGROUP, SUM(QTY) AS SUM_QTY
FROM KOPO_CHANNEL_RESULT
WHERE 1=1
GROUP BY PRODUCTGROUP
ORDER BY DECODE (PRODUCTGROUP,'TV',1,'APS',2,'MOBILE',3,'REF',4,'WM',5)
-- CASE WHEN 사용
SELECT PRODUCTGROUP, SUM(QTY) AS SUM_QTY
FROM KOPO_CHANNEL_RESULT
WHERE 1=1
GROUP BY PRODUCTGROUP
ORDER BY CASE WHEN PRODUCTGROUP = 'TV' THEN 1
WHEN PRODUCTGROUP = 'APS' THEN 2
WHEN PRODUCTGROUP = 'MOBILE' THEN 3
WHEN PRODUCTGROUP = 'TV' THEN 4
WHEN PRODUCTGROUP = 'WM' THEN 5 END
SELECT PRODUCTGROUP, SUM(QTY) AS SUM_QTY
FROM KOPO_CHANNEL_RESULT
WHERE 1=1
GROUP BY PRODUCTGROUP
ORDER BY CASE WHEN PRODUCTGROUP = 'TV' THEN 1
WHEN PRODUCTGROUP = 'APS' THEN 2
WHEN PRODUCTGROUP = 'MOBILE' THEN 3
WHEN PRODUCTGROUP = 'TV' THEN 4
ELSE 5 END
-- 5. [SQL함수] - 다음 중 대문자로 변경하기 위한 함수는?
-- 답 : UPPER
-- 6. [SQL함수] - 실제로 보이기엔 STR인데 길이함수를 활용하여 LENGTH('컬럼명') 10 이라는 숫자가 나왔다. 이때 공백을 제거하는 함수는?
-- LPAD, TRIM, SUBSTR 답 : TRIM
-- SUB Query (1. FROM, 2. WHERE, 3. SELECT, 4. WITH AS)
-- 1. FROM SUB Query
SELECT A.*
FROM (
SELECT *
FROM KOPO_CHANNEL_SEASONALITY_NEW
WHERE 1=1
AND REGIONID = 'A01'
) A -- () 안의 조회한 내용을 'A'라는 별칭으로 두겠다.
-- 검증하기
SELECT DISTINCT REGIONID FROM KOPO_CHANNEL_SEASONALITY_NEW -- 원본에는 REGIONID가 여러개 존재.
SELECT DISTINCT REGIONID -- FROM SUB Query에 의해 A01만 조회된다.
FROM (
SELECT *
FROM KOPO_CHANNEL_SEASONALITY_NEW
WHERE 1=1
AND REGIONID = 'A01'
) A -- () 안의 조회한 내용을 'A'라는 별칭으로 두겠다.
SELECT *
FROM KOPO_CHANNEL_SEASONALITY_NEW
WHERE 1=1
AND PRODUCT IN (SELECT DISTINCT PRODUCT FROM KOPO_CHANNEL_RESULT_NEW)
SELECT DISTINCT PRODUCT
FROM KOPO_CHANNEL_SEASONALITY_NEW
WHERE 1=1
SELECT DISTINCT PRODUCT
FROM KOPO_CHANNEL_RESULT_NEW
WHERE 1=1
SELECT A.*,
(SELECT AVG(QTY)
FROM KOPO_CHANNEL_SEASONALITY_NEW
WHERE REGIONID = A.REGIONID
AND PRODUCT = A.PRODUCT
GROUP BY A.REGIONID, A.PRODUCT) AS SUM_QTY
FROM KOPO_CHANNEL_SEASONALITY_NEW A
SELECT *
FROM KOPO_CHANNEL_SEASONALITY_NEW
WHERE 1=1
AND PRODUCT = 'PRODUCT47'
AND YEARWEEK = 201402;
-- 여기에다가~~~ 할인가격 -> 제수가 0인 경우를 평균으로?
SELECT A.*, A.IR/A.MAP_PRICE
FROM KOPO_PROMOTION_HK A
-- 여기에다가~~~ MAP_PRICE가 0인 경우, PRODUCT의 평균 가격으로 대체한다.
-- 내꺼
SELECT A.*,
CASE WHEN A.MAP_PRICE = 0 OR A.MAP_PRICE IS NULL THEN
(
SELECT AVG(MAP_PRICE)
FROM KOPO_PROMOTION_HK
WHERE REGIONID = A.REGIONID
AND PRODUCT = A.PRODUCT
GROUP BY REGIONID, PRODUCT)
ELSE A.MAP_PRICE END AS MAP_PRICE
FROM KOPO_PROMOTION_HK A
-- ITEM이 다른게 있네...
SELECT A.*,
CASE WHEN A.MAP_PRICE = 0 OR A.MAP_PRICE IS NULL THEN
(
SELECT AVG(MAP_PRICE)
FROM KOPO_PROMOTION_HK
WHERE REGIONID = A.REGIONID
AND PRODUCT = A.PRODUCT
AND ITEM = A.ITEM
GROUP BY REGIONID, PRODUCT, ITEM)
ELSE A.MAP_PRICE END AS MAP_PRICE
FROM KOPO_PROMOTION_HK A
--민석
SELECT A.*,
CASE WHEN A.MAP_PRICE=0 OR A.MAP_PRICE IS NULL THEN
(
SELECT AVG(MAP_PRICE)
FROM KOPO_PROMOTION_HK B
WHERE B.PRODUCT=A.PRODUCT GROUP BY B.PRODUCT)
ELSE A.MAP_PRICE
END AS MAP_PRICE_NEW
FROM KOPO_PROMOTION_HK A
WHERE 1=1
-- 상민
SELECT
A.REGIONID,
A.ITEM,
A.TARGETWEEK,
A.PLANWEEK,
NVL (A.MAP_PRICE,0) AS MAP_PRICE,
A.IR,
A.PRODUCT
FROM KOPO_PROMOTION_HK A
SELECT
A.REGIONID,
A.ITEM,
A.TARGETWEEK,
A.PLANWEEK,
NVL2 (A.MAP_PRICE,A.MAP_PRICE,0) AS MAP_PRICE,
A.IR,
A.PRODUCT
FROM KOPO_PROMOTION_HK A
-- 0을 빼고 평균을 구할지, 0을 포함하고 평균을 구할지도 고민해야한다.
SELECT B.*,
CASE WHEN B.MAP_PRICE = 0 THEN ( SELECT AVG(MAP_PRICE)
FROM KOPO_PROMOTION_HK C
WHERE 1=1
AND C.REGIONID = B.REGIONID
AND C.ITEM = B.ITEM
)
ELSE B.MAP_PRICE END AS MAP_PRICE_NEW
FROM (
SELECT
A.REGIONID,
A.ITEM,
A.TARGETWEEK,
A.PLANWEEK,
NVL (A.MAP_PRICE,0) AS MAP_PRICE,
A.IR,
A.PRODUCT
FROM KOPO_PROMOTION_HK A
) B
WHERE 1=1
AND REGIONID = 'A01'
AND ITEM = 'ITEM00533'
GROUP BY REGIONID, ITEM
SELECT *
FROM (
SELECT * FROM KOPO_CHANNEL_RESULT
WHERE 1=1
AND PRODUCTGROUP = 'TV'
) A
WITH A AS
(
SELECT * FROM KOPO_CHANNEL_RESULT
WHERE 1=1
AND PRODUCTGROUP = 'TV'
)
SELECT * FROM A;
-- 데이터 조인
SELECT * -- 124,658건
FROM KOPO_CHANNEL_SEASONALITY_NEW
SELECT * -- 73건
FROM KOPO_REGION_MST
SELECT * -- 124,658 X 73 = 9,100,034건
FROM KOPO_CHANNEL_SEASONALITY_NEW,
KOPO_REGION_MST
-- ANSI METHOD
SELECT A.*, B.REGIONNAME
FROM KOPO_CHANNEL_SEASONALITY_NEW A
INNER JOIN KOPO_REGION_MST B
ON A.REGIONID = B.REGIONID
SELECT A.*, B.* -- 124,344건 (REGIONID 컬럼을 기준으로, A와 B의 교집합.)
FROM KOPO_CHANNEL_SEASONALITY_NEW A
INNER JOIN KOPO_REGION_MST B
ON A.REGIONID = B.REGIONID
SELECT A.*, B.* -- 124,658건 (REGIONID 컬럼을 기준으로, A를 기준으로 B를 조인. B는 없고 A에만 있는 데이터 314건이 추가로 포함됨.)
FROM KOPO_CHANNEL_SEASONALITY_NEW A
LEFT JOIN KOPO_REGION_MST B
ON A.REGIONID = B.REGIONID
SELECT A.*, B.* -- 124,348건 (REGIONID 컬럼을 기준으로, B를 기준으로 A를 조인. A는 없고 B에만 있는 데이터 4건이 추가로 포함됨.)
FROM KOPO_CHANNEL_SEASONALITY_NEW A
RIGHT JOIN KOPO_REGION_MST B
ON A.REGIONID = B.REGIONID
SELECT A.*, B.* -- 124,662건 (REGIONID 컬럼을 기준으로, A와 B의 합칩합. 124,344 + 314 + 4 = 124,662)
FROM KOPO_CHANNEL_SEASONALITY_NEW A
FULL OUTER JOIN KOPO_REGION_MST B
ON A.REGIONID = B.REGIONID
-- 실습
SELECT * FROM KOPO_JOIN_TEST
SELECT * FROM KOPO_JOIN_TEST2
SELECT A.*, B.*
FROM KOPO_JOIN_TEST A
INNER JOIN KOPO_JOIN_TEST2 B
ON (A.REGIONID = B.REGIONID) AND (A.SALESID = B.SALESID)
SELECT A.*, B.*
FROM KOPO_JOIN_TEST A
LEFT JOIN KOPO_JOIN_TEST2 B
ON (A.REGIONID = B.REGIONID) AND (A.SALESID = B.SALESID)
SELECT A.*, B.*
FROM KOPO_JOIN_TEST A
RIGHT JOIN KOPO_JOIN_TEST2 B
ON (A.REGIONID = B.REGIONID) AND (A.SALESID = B.SALESID)
SELECT A.*, B.*
FROM KOPO_JOIN_TEST A
FULL OUTER JOIN KOPO_JOIN_TEST2 B
ON (A.REGIONID = B.REGIONID) AND (A.SALESID = B.SALESID)
-- PIVOT
SELECT * FROM DT_RESULT_FINAL2 -- RECORD COUNT : 44
--WHERE SALES = 2100
ORDER BY PRODUCTGROUP, PRODUCT, ITEM, YEARWEEK, "MEASURE" -- MEASURE 가 예약어인데 컬럼명으로 들어가있어서 쌍따옴표를 해준다.
--CREATE TABLE DT_RESULT_FINAL3 AS -- 피벗된 테이블을 'DT_RESULT_FINAL3'라는 테이블로 생성한다.
SELECT -- RECORD COUNT : 22 (PIVOT에 의해 동일한 PRODUCTGROUP, PRODUCT, ITEM, YEARWEEK, MEASURE을 가지면서 서로 다른 2개의 MEASURE(REAL_QTY, PREDICTION_QTY)를 각각 분리해 컬럼으로 만들어주어 절반으로 줄었다.
*
FROM (
SELECT *
FROM DT_RESULT_FINAL2
)
PIVOT(
SUM(SALES)
FOR MEASURE IN
('REAL-QTY' AS REAL_QTY,'PREDICTION-QTY' AS PREDICTION_QTY)
)
ORDER BY PRODUCTGROUP, PRODUCT, ITEM, YEARWEEK
SELECT
*
FROM (
SELECT *
FROM DT_RESULT_FINAL2
)
PIVOT(
AVG(SALES) -- 피벗 후 row에 들어갈 'REAR_QTY'나 'PREDICTION_QTY'가 2개 이상일 수 있기 때문에 반드시 SUM이나 AVG를 해서 넣어줘야한다.
FOR MEASURE IN
('REAL-QTY' AS REAL_QTY,'PREDICTION-QTY' AS PREDICTION_QTY)
)
ORDER BY PRODUCTGROUP, PRODUCT, ITEM, YEARWEEK
-- UNPIVOT
WITH T AS(
SELECT * FROM DT_RESULT_FINAL3
)
SELECT * FROM T
UNPIVOT(QTY FOR MEASURE IN(REAL_QTY, PREDICTION_QTY))
-- UNPIVOT 검증하기
SELECT
"MEASURE",
PRODUCTGROUP,
PRODUCT,
ITEM,
YEARWEEK,
SALES
FROM DT_RESULT_FINAL2
WHERE 1=1
AND PRODUCTGROUP = 'PG02'
AND ITEM = 'ITEM0115'
AND YEARWEEK = 201632
ORDER BY PRODUCTGROUP, PRODUCT, ITEM, YEARWEEK, "MEASURE"
WITH T AS(
SELECT * FROM DT_RESULT_FINAL3
)
SELECT
"MEASURE",
PRODUCTGROUP,
PRODUCT,
ITEM,
YEARWEEK,
QTY
FROM T
UNPIVOT(QTY FOR MEASURE IN(REAL_QTY, PREDICTION_QTY))
WHERE 1=1
AND PRODUCTGROUP = 'PG02'
AND ITEM = 'ITEM0115'
AND YEARWEEK = 201632
ORDER BY PRODUCTGROUP, PRODUCT, ITEM, YEARWEEK, "MEASURE"
SELECT * FROM PRO_FCST_RESULT_1WEEK
WHERE 1=1
AND PRD_SEG1 = 'PRDA'
AND PRD_SEG2 = 'PRDA1'
AND PRD_SEG3 = '313'
