| 일 | 월 | 화 | 수 | 목 | 금 | 토 |
|---|---|---|---|---|---|---|
| 1 | 2 | 3 | ||||
| 4 | 5 | 6 | 7 | 8 | 9 | 10 |
| 11 | 12 | 13 | 14 | 15 | 16 | 17 |
| 18 | 19 | 20 | 21 | 22 | 23 | 24 |
| 25 | 26 | 27 | 28 | 29 | 30 | 31 |
- R/3
- S/4 HANA
- Where
- DateDiff
- over()
- json
- ERP
- %_%
- SQL
- 중복 컬럼
- limit
- SERVICE_KEY_IS_NOT_REGISTERED_ERROR
- SAP HANA
- group by
- CSV
- between
- stratascratch
- union
- r/2
- 오블완
- join
- asc
- spa
- dense_rank()
- order by
- left join
- 공공데이터
- 티스토리챌린지
- HAVING
- desc
- Today
- Total
목록SQL (8)
RE:cord
-----If not using union...SELECT ProductID, SUM(OrderQty) AS TotalQty, COUNT(OrderQty) NoOfSales, CASE WHEN AVG(OrderQty) 6 THEN 'High quantity' END AS AvgQtyDescribeFROM Sales.SalesOrderDetail AS SOD JOIN Sales.SalesOrderHeader AS SOH ON SOD.SalesOrderID = SOH.SalesOrderIDWHERE SOH.OrderDate BETWEEN '2012-05-01' AND '2012-05-31'GROUP BY SOD.ProductIDORDER BY AVG(OrderQty) DESC -----If using..
[SQL] Analyzing User Retention (유저 리텐션 분석) (DATEDIFF / BETWEEN AND / HAVING)
* 문제 애매한 점 정의 - reservation date가 따로 없음. ⇒ activity_date를 reservation date로 취급 - 가입일에만 접속 로그가 남아있는 사용자 처리 조건 명시 x -> 가입일에만 접속한 사람도 활동유저로 집계 SELECT ua.user_id, ua.activity_date, COUNT(ua.activity_date)FROM user_profiles AS up JOIN user_activities AS ua ON up.user_id = ua.user_idWHERE DATEDIFF(activity_date, signup_date) BETWEEN 0 AND 30GROUP BY up.user_id--HAVING COUNT(up.user_id)>0-- * ..
SELECT *FROM moviesWHERE title LIKE 'Christmas' OR title LIKE 'Santa'; * 정확하게 'Christmas' or 'Santa'가 title이어야 출력. It would be out if the title is 'Christmas' or 'Santa'. SELECT *FROM moviesWHERE title LIKE '%Christmas%' OR title LIKE '%Santa%'; * 'Christmas' or 'Santa'가 title에 포함되어 있으면 출력. It would be out if the title contained 'Christmas' or 'Santa.'
[SQL] Finding the Nth Highest Values (JOIN/ORDER BY /LIMIT/DENSE_RANK () OVER)
-- 1. join / 2. selectSELECT worker_title, salaryFROM worker JOIN title ON worker.worker_id=title.worker_ref_idWHERE salary >= (SELECT MAX(salary) FROM worker) -- using LIMITSELECT *FROM workerWHERE salary >= ( SELECT DISTINCT salary FROM worker ORDER BY salary DESC LIMIT 4, 1);-- using DENSE_RANKSELECT first_name, salary, salary_rankFROM (SELECT first_name,salary, DENSE_RANK() OVER..
[SQL] Count the number of group (GROUP BY / JOIN / LEFT JOIN / SUM)
-- subQ: Only MacBookPro user's data-- mainQ: event name, event freq -> DESC orderSELECT event_name, COUNT(event_name) AS event_freqFROM (SELECT * FROM playbook_events WHERE device = 'macbook pro') AS MBP_userGROUP BY event_nameORDER BY event_freq DESC; --1.left join with 'host_id'--2.make group by 'nationality'--3.count n_beds of each groups--4. sort avail_beds DESCSELECT nati..
Company Research; SAP 1) Outline SAP is a German company founded by five engineers who had worked at IBM and its headquarters is in German Walldorf. Their idea was to create standard enterprise software that integrated all business processes and enabled data processing in real time, and it became a reality. SAP established the global standard for enterprise resource planning (ERP) software. SA..
[SQL] Find the Nth group (DENSE_RANK / OVER / SUBQUERY)
-- Mainquery : SLECT shipment_id, total_weight FROM (subquery) subquery_name WHERE rank=3-- Subquery : SELECT shipment_id, a, b FROM amazon_shipment GROUP BY shipment_id SELECT shipment_id, total_weight, heavy_numFROM( SELECT shipment_id, SUM(weight) AS total_weight, DENSE_RANK() OVER (ORDER BY SUM(weight) DESC) AS heavy_num FROM amazon_shipment GROUP BY shipment_id) rank..
[SQL] AVG / GROUP BY / ORDER BY DESC
SELECT AVG(no_of_likes) FROM fb_posts; SELECT education, AVG(salary) FROM google_salaries GROUP BY education; SELECT * FROM movie_catalogue ORDER BY duration DESC; * Ascending: ORDER BY column / ORDER BY column ASC* Descending: ORDER BY column DESC