구름 아카이브

언젠가 활용될 지 모를 sql 고유값 만들기 아마 LEFT, INNER 등 조인을 통해 테이블 변환이 필요할 때 활용할 수 있을 것 같다. CONCAT의 괄호 끝에 ' 넣어주고, 컬럼들 사이에 ,',', 넣어주면 끝이다. 전체 row갯수와 중복값을 제거한 row갯수가 같을 때만 활용해야한다. ex) SELECT ONLINE_SALE_ID ,USER_ID ,PRODUCT_ID ,SALES_AMOUNT ,concat(online_sale_id,',',user_id,',',PRODUCT_ID,',',SALES_AMOUNT) AS new FROM ONLINE_SALE 전체 row 갯수 122 새로운 컬럼 new가 생성되고, 문자열 조합으로 이루어진 값을 확인할 수 있다.

SQL Aggregate 함수 = SUM, COUNT, AVG... AVG, COUNT는 null은 세지 않음 COUNT -------------------------------------------------------------------------- SELECT COUNT(*) FROM Products >>> 프로덕트의 행들, 데이터 레코드의 개수 세기, null 값 포함 SELECT COUNT(Price) FROM Products AS COUNT(Price) >>> 특정 컬럼의 행 갯수 SELECT COUNT(DISTINCT Price) FROM Products AS COUNT(Price) >>> 중복값은 제외하고 셀 때 + null 값 미포함 SUM, AVG(null 계산에서 제외처리됨 ) --..

시간 더하기, 빼기 -------------------------------------------------------------------------- DATE_ADD SELECT DATE_ADD(NOW(), INTERVAL 1 SECOND) SELECT DATE_ADD(NOW(), INTERVAL 1 MINUTE) SELECT DATE_ADD(NOW(), INTERVAL 1 HOUR) SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH) SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR) SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR) DATE_SUB SELECT DA..

https://leetcode.com/problems/department-top-three-salaries/ -- RANK(), DENSE_RANK() SELECT t.department , t.employee , t.salary FROM( SELECT department.name AS dapartment , employee.name AS employee , employee.salary , DENSE_RANK() OVER (PARTITION BY departmentid ORDER BY salary DESC) AS dr FROM Employee INNER JOIN dapartment ON employee.departmentId = dapartment.id ) t WHERE t.dr

https://leetcode.com/problems/department-highest-salary/ *** SELECT에서 연산한 결과물은 WHERE 절에서 사용할 수 없음 SELECT department.name AS Department , sub.name AS Employee , sub.max_salary AS Salary FROM( SELECT id , name , salary , departmentid , MAX(salary) OVER (PARTITION BY departmentId) AS max_salary FROM employee -- WHERE salary = max_salary >>> SELECT에서 연산한 결과물은 WHERE 절에서 사용할 수 없음 ) as sub INNER JOIN D..

https://leetcode.com/problems/consecutive-numbers/ -- Use Window function -- *LEAD, *LAG SELECT DISTINCT l.NUM AS ConsecutiveNums SELECT NUM , LEAD(NUM,1) OVER (ORDER BY id) AS next , LEAD(NUM,2) OVER (ORDER BY id ) AS afternext FROM logs )l WHERE l.Num=next AND l.next=l.afternext

Leetcode 177 - Nth Highest Salary https://leetcode.com/problems/nth-highest-salary/description/ CASE 문 CREATE FUNCTION getNthHighestSalary (N INT) RETURNS INT BEGIN RETURN ( SELECT CASE WHEN COUNT(sub.Salary) >> 건이 순차적으로 실행되어야 하는 상황에는 CASE가 적합 (구간을 특정 기준으로 ..

regxone.com/lesson/introdution_abcs. >>> 연습사이트 regxr.com Weather Observation Station 6 https://www.hackerrank.com/challenges/weather-observation-station-6/problem?h_r=internal-search 기존 풀이 SELECT DISTINCT city FROM station WHERE city LIKE 'a%' OR city LIKE 'e%' OR city LIKE 'i%' OR city LIKE 'o%' OR city LIKE 'u%' -------------------------------------------------------------------------------- 정..

https://www.hackerrank.com/challenges/what-type-of-triangle/problem SELECT CASE WHEN A=B AND B=C THEN 'Equilateral' WHEN A+B

Symmetric Pairs - Medium https://www.hackerrank.com/challenges/symmetric-pairs/problem SELECT X, YG FROM Functions WHERE X=Y GROUP BY X,Y HAVING COUNT(*)>=2 UNION SELECT s1.X, s1.Y FROM functions AS s1 INNER JOIN functions AS s2 ON s1.X = s2.Y AND s1.Y = s2.X WHERE s1.X < s1.Y ORDER BY X