하나둘셋넷

SQL GROUP BY] 저자 별 카테고리 별 매출액 집계하기

SQL SUM, MAX, MIN] 동물 수 구하기

-- 코드를 입력하세요
SELECT COUNT(ANIMAL_ID) as count
    FROM ANIMAL_INS I

SQL String, Date] 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기 LIMIT 1

-- 코드를 입력하세요
SELECT CONCAT('/home/grep/src/',B.BOARD_ID,'/',F.FILE_ID,F.FILE_NAME,F.FILE_EXT) as FILE_PATH
    FROM USED_GOODS_BOARD B
    INNER JOIN(
        SELECT *
        FROM USED_GOODS_FILE
    ) as F on B.BOARD_ID = F.BOARD_ID
    WHERE B.BOARD_ID = (
        SELECT BOARD_ID
        FROM USED_GOODS_BOARD
        ORDER BY VIEWS DESC
        LIMIT 1
    )
    
    ORDER BY F.FILE_ID DESC

SQL 이론] UNION_ALL

-- 코드를 입력하세요
SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') as SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
    FROM (
        SELECT O.SALES_DATE, O.PRODUCT_ID, O.USER_ID, O.SALES_AMOUNT
        FROM ONLINE_SALE O
        WHERE O.SALES_DATE >= '2022-03-01' AND O.SALES_DATE <'2022-04-01'
        
        UNION ALL
        
        SELECT F.SALES_DATE, F.PRODUCT_ID, NULL as USER_ID, F.SALES_AMOUNT
        FROM OFFLINE_SALE F
        WHERE F.SALES_DATE >= '2022-03-01' AND F.SALES_DATE <'2022-04-01'
    ) AS S
    ORDER BY SALES_DATE, PRODUCT_ID, USER_ID

SQL SELECT] 오프라인/온라인 판매 데이터 통합하기

-- 코드를 입력하세요
SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') as SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
    FROM (
        SELECT O.SALES_DATE, O.PRODUCT_ID, O.USER_ID, O.SALES_AMOUNT
        FROM ONLINE_SALE O
        WHERE O.SALES_DATE >= '2022-03-01' AND O.SALES_DATE <'2022-04-01'
        UNION ALL
        SELECT F.SALES_DATE, F.PRODUCT_ID, NULL as USER_ID, F.SALES_AMOUNT
        FROM OFFLINE_SALE F
        WHERE F.SALES_DATE >= '2022-03-01' AND F.SALES_DATE <'2022-04-01'
    ) AS S
    ORDER BY SALES_DATE, PRODUCT_ID, USER_ID

SQL JOIN] 있었는데요 없었습니다

-- 코드를 입력하세요
SELECT I.ANIMAL_ID, I.NAME
    FROM ANIMAL_INS I
    INNER JOIN(
        SELECT ANIMAL_ID,DATETIME
        FROM ANIMAL_OUTS
    ) AS O ON I.ANIMAL_ID = O.ANIMAL_ID
    WHERE I.DATETIME > O.DATETIME
    ORDER BY I.DATETIME

SQL IS NULL] 이름이 없는 동물의 아이디

-- 코드를 입력하세요
SELECT ANIMAL_ID
    FROM ANIMAL_INS
    WHERE NAME IS NULL

SQL GROUP BY] 카테고리 별 도서 판매량 집계하기

-- 코드를 입력하세요
SELECT b.CATEGORY, sum(s.SALES) as TOTAL_SALES
    FROM BOOK b
    INNER JOIN(
        SELECT BOOK_ID, SALES, SALES_DATE
        FROM BOOK_SALES
    ) as s on b.BOOK_ID = s.BOOK_ID
    WHERE s.SALES_DATE >= '2022-01-01' AND s.SALES_DATE < '2022-02-01'
    GROUP BY b.CATEGORY
    ORDER BY b.CATEGORY

데이터 전처리] 1,234 등 숫자에서 쉼표를 제거하고 숫자형으로 형식 변경, 빈 칸 np.nan으로 대체 및 제거, str.replace(',', '').astype(float), np.nan, subset

seoul_monthly_2023['대여건수'] = seoul_monthly_2023['대여건수'].str.replace(',', '').astype(float)

seoul_monthly_2023['반납건수'] = seoul_monthly_2023['반납건수'].str.replace(',', '')
seoul_monthly_2023['반납건수'] = seoul_monthly_2023['반납건수'].str.replace(' - ', '')
seoul_monthly_2023['반납건수'].replace('', np.nan, inplace = True)
seoul_monthly_2023.dropna(subset=['반납건수'], inplace= True)
seoul_monthly_2023['반납건수'] = seoul_monthly_2023['반납건수'].astype(float)