SQL - 20250508

2025. 5. 8. 22:42ยทDB/SQL

 

 

 

๐ŸŒซ๏ธ ์ˆ˜์› ์ง€์—ญ ์—ฐ๋„๋ณ„ ํ‰๊ท  ๋ฏธ์„ธ๋จผ์ง€ ๋†๋„ โ€“ ์ฟผ๋ฆฌ ๋ถ„์„

SELECT EXTRACT(YEAR FROM YM) AS YEAR, 
       ROUND(AVG(PM_VAL1), 2) AS PM10,
       ROUND(AVG(PM_VAL2), 2) AS 'PM2.5'
FROM AIR_POLLUTION
WHERE LOCATION2 = '์ˆ˜์›'
GROUP BY EXTRACT(YEAR FROM YM)
ORDER BY YEAR;

 

 


 

โœ… EXTRACT(YEAR FROM YM)

 

  • YM์—์„œ ์—ฐ๋„๋งŒ ์ถ”์ถœ
  • GROUP BY์™€ SELECT์— ๋™์ผํ•˜๊ฒŒ ์‚ฌ์šฉํ•ด ์—ฐ๋„๋ณ„ ์ง‘๊ณ„ ์ˆ˜ํ–‰

 


 

โœ… ROUND(AVG(PM_VAL1), 2)

 

  • ๋ฏธ์„ธ๋จผ์ง€(PM10)์˜ ํ‰๊ท ๊ฐ’์„ ์†Œ์ˆ˜ ๋‘˜์งธ ์ž๋ฆฌ๊นŒ์ง€ ๋ฐ˜์˜ฌ๋ฆผ
  • AVG()๋Š” ์ง‘๊ณ„ ํ•จ์ˆ˜, ROUND(..., 2)๋กœ ์ •๋ฐ€๋„ ์กฐ์ •

 


 

โœ… ROUND(AVG(PM_VAL2), 2) AS โ€˜PM2.5โ€™

 

  • ์ดˆ๋ฏธ์„ธ๋จผ์ง€(PM2.5) ํ‰๊ท ๊ฐ’ ๊ณ„์‚ฐ
  • ์ปฌ๋Ÿผ๋ช…์„ ๋ฌธ์ž์—ด 'PM2.5'๋กœ ์ถœ๋ ฅ

 


 

โœ… WHERE LOCATION2 = โ€˜์ˆ˜์›โ€™

 

  • LOCATION2๊ฐ€ '์ˆ˜์›'์ธ ์ง€์—ญ๋งŒ ํ•„ํ„ฐ๋ง
  • ์—ฐ๋„์™€ ๋ฌด๊ด€ํ•˜๊ฒŒ ๋ชจ๋“  ์ˆ˜์› ๋ฐ์ดํ„ฐ๋ฅผ ๊ธฐ์ค€์œผ๋กœ ์ง‘๊ณ„

 


 

โœ… GROUP BY EXTRACT(YEAR FROM YM)

 

  • ์—ฐ๋„ ๊ธฐ์ค€์œผ๋กœ ๊ทธ๋ฃนํ•‘
  • ๊ฐ ์—ฐ๋„๋ณ„ ํ‰๊ท ๊ฐ’ ๊ณ„์‚ฐ์„ ์œ„ํ•œ ํ•„์ˆ˜ ๊ตฌ๋ฌธ

 


 

๐Ÿ’ธ ๊ฐ€๊ฒฉ๋Œ€๋ณ„ ์ƒํ’ˆ ๊ฐœ์ˆ˜๊ตฌํ•˜๊ธฐ โ€“ ์ฟผ๋ฆฌ ๋ถ„์„

SELECT FLOOR(PRICE/10000)*10000 AS PRICE_GROUP,
       COUNT(*) AS PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP;

 

 


 

โœ… FLOOR(PRICE / 10000) * 10000 AS PRICE_GROUP

 

  • PRICE๋ฅผ 10,000์› ๋‹จ์œ„๋กœ ๋‚ด๋ฆผ ์ฒ˜๋ฆฌ
  • ์˜ˆ: 27,500 โ†’ 20,000, 39,900 โ†’ 30,000
  • ๋™์ผํ•œ ๊ฐ€๊ฒฉ๋Œ€๋ฅผ ํ•˜๋‚˜์˜ ๊ทธ๋ฃน์œผ๋กœ ๋ฌถ๊ธฐ ์œ„ํ•œ ์ฒ˜๋ฆฌ

 


 

โœ… COUNT(*)

 

  • ๊ฐ ๊ฐ€๊ฒฉ ๊ทธ๋ฃน์— ์†ํ•˜๋Š” ์ƒํ’ˆ ๊ฐœ์ˆ˜ ๊ณ„์‚ฐ

 


 

โœ… GROUP BY PRICE_GROUP

 

  • ๋‚ด๋ฆผ๋œ ๊ฐ€๊ฒฉ๋Œ€๋ฅผ ๊ธฐ์ค€์œผ๋กœ ๊ทธ๋ฃนํ•‘

 

 

 

'DB > SQL' ์นดํ…Œ๊ณ ๋ฆฌ์˜ ๋‹ค๋ฅธ ๊ธ€

SQL - 20250514  (0) 2025.05.14
SQL - 20250513  (2) 2025.05.13
SQL - 20250506  (0) 2025.05.06
SQL 20241005  (1) 2024.10.05
SQL 20241003  (2) 2024.10.03
'DB/SQL' ์นดํ…Œ๊ณ ๋ฆฌ์˜ ๋‹ค๋ฅธ ๊ธ€
  • SQL - 20250514
  • SQL - 20250513
  • SQL - 20250506
  • SQL 20241005
Geisha
Geisha
๊ฐœ๋ฐœ ์ผ๊ธฐ
  • Geisha
    Geisha
    Geisha
  • ์ „์ฒด
    ์˜ค๋Š˜
    ์–ด์ œ
    • ๋ถ„๋ฅ˜ ์ „์ฒด๋ณด๊ธฐ (326)
      • Algorithm & Data Structures (246)
        • BOJ (104)
        • SWEA (1)
        • Programers (137)
        • Data Structures (3)
      • DB (27)
        • SQL (21)
        • RDBMS (2)
      • Java (1)
        • Class (1)
      • Spring (5)
        • Spring MVC (1)
        • Annotations (1)
      • CS (36)
        • ์šด์˜์ฒด์ œ (13)
        • ๋„คํŠธ์›Œํฌ (5)
      • Tool (6)
        • Git (5)
        • AWS (1)
      • Project (1)
  • ๋ธ”๋กœ๊ทธ ๋ฉ”๋‰ด

    • ํ™ˆ
    • ํƒœ๊ทธ
    • ๋ฐฉ๋ช…๋ก
  • ๋งํฌ

  • ๊ณต์ง€์‚ฌํ•ญ

  • ์ธ๊ธฐ ๊ธ€

  • ํƒœ๊ทธ

    ์ „์œ„์ˆœํšŒ
    Dijkstra
    ์œ ๋‹ˆ์˜จํŒŒ์ธ๋“œ
    ๊ตฌํ˜„
    Union-Find
    BFS
    Java
    ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค
    PriorityQueue
    ์Šคํƒ
    ๋‹ค์ต์ŠคํŠธ๋ผ
    ๊ฒฝ๋กœ์••์ถ•
    unionfind
    ๊ณจ๋“œ
    SQL
    ์ด๋ถ„ํƒ์ƒ‰
    ์•Œ๊ณ ๋ฆฌ์ฆ˜
    baekjoon
    algorithm
    Stack
    ๋ฐฑํŠธ๋ž˜ํ‚น
    programmers
    dp
    ํ›„์œ„์ˆœํšŒ
    ํˆฌํฌ์ธํ„ฐ
    ๋ฐฑ์ค€
    DynamicProgramming
    ๋™์ ๊ณ„ํš๋ฒ•
    binarySearch
    dfs
  • ์ตœ๊ทผ ๋Œ“๊ธ€

  • ์ตœ๊ทผ ๊ธ€

  • hELLOยท Designed By์ •์ƒ์šฐ.v4.10.0
Geisha
SQL - 20250508
์ƒ๋‹จ์œผ๋กœ

ํ‹ฐ์Šคํ† ๋ฆฌํˆด๋ฐ”

๋‹จ์ถ•ํ‚ค

๋‚ด ๋ธ”๋กœ๊ทธ

๋‚ด ๋ธ”๋กœ๊ทธ - ๊ด€๋ฆฌ์ž ํ™ˆ ์ „ํ™˜
Q
Q
์ƒˆ ๊ธ€ ์“ฐ๊ธฐ
W
W

๋ธ”๋กœ๊ทธ ๊ฒŒ์‹œ๊ธ€

๊ธ€ ์ˆ˜์ • (๊ถŒํ•œ ์žˆ๋Š” ๊ฒฝ์šฐ)
E
E
๋Œ“๊ธ€ ์˜์—ญ์œผ๋กœ ์ด๋™
C
C

๋ชจ๋“  ์˜์—ญ

์ด ํŽ˜์ด์ง€์˜ URL ๋ณต์‚ฌ
S
S
๋งจ ์œ„๋กœ ์ด๋™
T
T
ํ‹ฐ์Šคํ† ๋ฆฌ ํ™ˆ ์ด๋™
H
H
๋‹จ์ถ•ํ‚ค ์•ˆ๋‚ด
Shift + /
โ‡ง + /

* ๋‹จ์ถ•ํ‚ค๋Š” ํ•œ๊ธ€/์˜๋ฌธ ๋Œ€์†Œ๋ฌธ์ž๋กœ ์ด์šฉ ๊ฐ€๋Šฅํ•˜๋ฉฐ, ํ‹ฐ์Šคํ† ๋ฆฌ ๊ธฐ๋ณธ ๋„๋ฉ”์ธ์—์„œ๋งŒ ๋™์ž‘ํ•ฉ๋‹ˆ๋‹ค.