Prerequisite

• SQLite Interface in python
  • insert records
  • execute query
• Pandas and SQLite?
  • table into Dataframe
  • DataFrame → table

SQL

Example: Bank Account

• find the number of accounts of each branch in each country
• output balance sum of each country in descending order

//////////해보기/////////

Interpreting complicated SQL

• example 1

SELECT binid,
	round(avg(cast(Fluo as float)),3) as Fluo,
	round(avg(cast(Oxygen as float)),3) as Oxygen,
	round(avg(cast(Nitrate_uM as float)),3) as Nitrate_uM,
	round(avg(cast(longitude as float)),3) as longitude,
	round(avg(cast(latitude as float)),3) as latitude
FROM(
	SELECT *,                           //아래의 모든 데이터+계산한 값 열 추가
		cast(floor(ts)+floor((ts-floor(ts))*24*60/binsize)*binsize/(24*60) as datetime) as binid
	FROM ( 
		SELECT *,                         //도쿄 데이터의 모든 데이터+
			cast(timestamp as float) as ts, //ts 열 추가
			5.0 as binsize                  //binsize 열 추가
		FROM Tokyo_4_merged_data_time
	) x
)bins
GROUP BY binid
ORDER BY binid ASC

• example 2

중간의 빨간 부분을 사용자 정의 함수로 빼자.

초록 부분도 사용자 정의 함수로 빼자.