60일차

Suhyeon Lee·2024년 12월 24일

내일배움캠프 Data 4기

목록 보기

60/80

CodeKata

SQL

181. Draw The Triangle 1

문제

P(R) represents a pattern drawn by Julia in R rows. The following pattern represents P(5):

*****
****
***
**
*

Write a query to print the pattern P(20).

→ ???이게 SQL로도 구현이 되나요???
→ 도저히 혼자서는 못 풀겠어서 다른 사람들이 적어 둔 힌트를 보고 풀었음

작성한 쿼리

WITH RECURSIVE numbers AS (
  SELECT 20 AS n 
  UNION ALL
  SELECT n - 1
  FROM numbers
  WHERE n > 1
)
SELECT 
  REPEAT('* ', n) AS pattern 
FROM
  numbers
;

※ WITH RECURSIVE 구문
: 가상 테이블을 생성하면서 가상 테이블 자신의 값을 참조하여 값을 결정할 때 사용

참고할 만한 다른 풀이

SET
  @number = 21;
SELECT
  REPEAT('* ', @number := @number - 1)
FROM 
  information_schema.tables 
;

The function works like that: repeat(string, times)
So, at the beginning we want to repeat the * symbol 20 times, but we also want to decrement its value by one in each iteration. That's why we start with 21.
So in the first iteration we apply 21-1=20, and we repeat the * symbol 20 times.
In the second we will apply 20-1=19, and we will repeat the * symbol 19 times.
User defined variables start with @ in MYSQL.
:= is an operator that does not require you to use the key word SET before it.
Basically @number := @number - 1 is saying decrease the @number user variable each time it's printed out. In pseudocode it would be number = number - 1.
In sql '=' is used for comparing/checking for equality (like '==' in c/c++/java) , in order to assign a value to a variable ':=' is used. Hope this helps.

I'm new here, but why do we need: information_schema.tables?
the actual values of information_schema.tables is not required as such but for running the sql query it has to refer to a table.
The INFORMATION_SCHEMA.TABLES view allows you to get information about all tables and views within a database. By default it will show you this information for every single table and view that is in the database.

Point
- @set =
- REPEAT()
- 대입연산자 :=
오라클에서는 CONNECT BY, MySQL에서는 변수 설정으로 임시 행을 생성한다고 함

-- ORACLE ver.
SELECT
    RPAD('*',(41-2 * LEVEL), ' *')
FROM
    DUAL
CONNECT BY
    LEVEL <= 20
;

-- MS SQL Server ver.
DECLARE @i INT = 20
WHILE (@i > 0) 
BEGIN
  PRINT REPLICATE('* ', @i) 
  SET @i = @i - 1
END

MS SQL
- DECLARE @local_variable : 본문에 변수를 선언
- WHILE : 반복문
- BEGIN, END : 그룹을 실행할 수 있도록 SQL문을 묶음
- REPLICATE : 지정한 횟수만큼 문자열 값을 반복

SELECT
  REPEAT('* ', @NUMBER := @NUMBER - 1)
FROM
  information_schema.tables, (SELECT @NUMBER:=21) t 
LIMIT 20
;

WITH RECURSIVE & ORDER BY

WITH RECURSIVE numbers AS (
  SELECT 1 AS n 
  UNION ALL
  SELECT n + 1
  FROM numbers
  WHERE n < 20
)
SELECT 
  REPEAT('* ', n) AS pattern 
FROM
  numbers
ORDER BY
  n DESC
;

182. Draw The Triangle 2

작성한 쿼리

SET 
  @number = 0
;
SELECT 
  REPEAT('* ', @number := @number + 1) 
FROM 
  information_schema.TABLES 
LIMIT 20
;

→ 181번에서 추가로 공부한 내용으로 풀었음
(처음 181번 풀었던 방법으로도 됨)

-- 1
WITH RECURSIVE numbers AS (
  SELECT 20 AS n 
  UNION ALL
  SELECT n - 1
  FROM numbers
  WHERE n > 1
)
SELECT 
  REPEAT('* ', n) AS pattern 
FROM
  numbers
ORDER BY
  n
;

-- 2
WITH RECURSIVE numbers AS (
  SELECT 1 AS n 
  UNION ALL
  SELECT n + 1
  FROM numbers
  WHERE n < 20
)
SELECT 
  REPEAT('* ', n) AS pattern 
FROM
  numbers
;