KASI Convert sql

DH 교육용 위키
이동: 둘러보기, 검색 
use hiblue

select * from 한국천문

 --solc
	--day
		--day class day
		select '<https://astro.kasi.re.kr/time#solcDay'+solc_yyyy+solc_mm+SOLC_DD+'>', '<http://www.w3.org/2002/07/owl#class>', '<http://dh.aks.ac.kr/ontologies/dhtime#Day>' from 한국천문
		--day dayOfWeek
		select '<https://astro.kasi.re.kr/time#solcDay'+solc_yyyy+solc_mm+SOLC_DD+'>','<http://www.w3.org/2006/time#dayofWeek>', 
			case when SOLC_WEEK='월' then '<http://www.w3.org/2006/time#Monday>'
				when SOLC_WEEK='화' then '<http://www.w3.org/2006/time#Tuesday>'
				when SOLC_WEEK='수' then '<http://www.w3.org/2006/time#Wednesday>'
				when SOLC_WEEK='목' then '<http://www.w3.org/2006/time#Thursday>'
				when SOLC_WEEK='금' then '<http://www.w3.org/2006/time#Friday>'
				when SOLC_WEEK='토' then '<http://www.w3.org/2006/time#Saturday>'
				else '<http://www.w3.org/2006/time#Sunday>' end from 한국천문
		--day label
		select '<https://astro.kasi.re.kr/time#solcDay'+solc_yyyy+solc_mm+SOLC_DD+'>','<http://www.w3.org/2000/01/rdf-schema#label>', '"'+solc_dd+'"^^xsd:nonNegativeInteger' from 한국천문
		select '<https://astro.kasi.re.kr/time#solcDay'+solc_yyyy+solc_mm+SOLC_DD+'>','<http://www.w3.org/2000/01/rdf-schema#label>', '"'+SOLC_WEEK+'"^^xsd:string' from 한국천문
		--day hasTRS solcCalander
		select '<https://astro.kasi.re.kr/time#solcDay'+solc_yyyy+solc_mm+SOLC_DD+'>', '<http://www.w3.org/2006/time#hasTRS>', '<https://astro.kasi.re.kr/time#solc>' from 한국천문
	    --day before day
		select '<https://astro.kasi.re.kr/time#solcDay'+a.solc_yyyy+a.solc_mm+a.SOLC_DD+'>', '<http://www.w3.org/2006/time#before>', '<https://astro.kasi.re.kr/time#solcDay'+b.solc_yyyy+b.solc_mm+b.SOLC_DD+'>' from (select solc_yyyy, solc_mm, SOLC_DD, solc_jd from 한국천문) a, (select solc_yyyy, solc_mm, SOLC_DD, solc_jd from 한국천문) b where a.SOLC_JD+1=b.SOLC_JD
		--day after day
		select '<https://astro.kasi.re.kr/time#solcDay'+a.solc_yyyy+a.solc_mm+a.SOLC_DD+'>', '<http://www.w3.org/2006/time#after>', '<https://astro.kasi.re.kr/time#solcDay'+b.solc_yyyy+b.solc_mm+b.SOLC_DD+'>' from (select solc_yyyy, solc_mm, SOLC_DD, solc_jd from 한국천문) a, (select solc_yyyy, solc_mm, SOLC_DD, solc_jd from 한국천문) b where a.SOLC_JD-1=b.SOLC_JD

	--month
		--month class month
		select distinct '<https://astro.kasi.re.kr/time#solcMon'+solc_yyyy+solc_mm+'>', '<http://www.w3.org/2002/07/owl#class>', '<http://dh.aks.ac.kr/ontologies/dhtime#Month>' from 한국천문
		--month label
		select distinct '<https://astro.kasi.re.kr/time#solcMon'+solc_yyyy+solc_mm+'>', '<http://www.w3.org/2000/01/rdf-schema#label>', '"'+solc_mm+'"^^xsd:nonNegativeInteger' from 한국천문
		--month hasTRS solcCalander
		select distinct '<https://astro.kasi.re.kr/time#solcMon'+solc_yyyy+solc_mm+'>', '<http://www.w3.org/2006/time#hasTRS>', '<https://astro.kasi.re.kr/time#solc>' from 한국천문
		--month hasBeginning
		select distinct '<https://astro.kasi.re.kr/time#solcMon'+solc_yyyy+solc_mm+'>', '<http://www.w3.org/2006/time#hasBeginning>','<https://astro.kasi.re.kr/time#solcDay'+solc_yyyy+solc_mm+min(SOLC_DD)+'>' from 한국천문 group by SOLC_YYYY, SOLC_MM
		--month hasEnd
		select distinct '<https://astro.kasi.re.kr/time#solcMon'+solc_yyyy+solc_mm+'>', '<http://www.w3.org/2006/time#hasEnd>','<https://astro.kasi.re.kr/time#solcDay'+solc_yyyy+solc_mm+max(SOLC_DD)+'>' from 한국천문 group by SOLC_YYYY, SOLC_MM
		--month days 월일수
			select distinct '<https://astro.kasi.re.kr/time#solcMon'+solc_yyyy+solc_mm+'>', '<http://www.w3.org/2006/time#days>','"'+cast(count(*) as nvarchar)+'"^^xsd:nonNegativeInteger' from 한국천문 group by SOLC_YYYY, SOLC_MM 
		--month inside day (inverse of month)
		select distinct '<https://astro.kasi.re.kr/time#solcMon'+solc_yyyy+solc_mm+'>', '<http://www.w3.org/2006/time#inside>','<https://astro.kasi.re.kr/time#solcDay'+solc_yyyy+solc_mm+SOLC_DD+'>' from 한국천문 

		--month before month
		select '<https://astro.kasi.re.kr/time#solcMon'+a.solc_yyyy+a.solc_mm+'>', '<http://www.w3.org/2006/time#before>', '<https://astro.kasi.re.kr/time#solcDay'+b.solc_yyyy+b.solc_mm+'>' from (select solc_yyyy, solc_mm, min(solc_jd) as first, max(solc_jd) as last from 한국천문 group by SOLC_YYYY, SOLC_MM ) a, (select solc_yyyy, solc_mm,  min(solc_jd) as first, max(solc_jd) as last from 한국천문 group by SOLC_YYYY, SOLC_MM ) b where a.last=b.first-1
		--month after month
		select '<https://astro.kasi.re.kr/time#solcMon'+a.solc_yyyy+a.solc_mm+'>', '<http://www.w3.org/2006/time#after>', '<https://astro.kasi.re.kr/time#solcDay'+b.solc_yyyy+b.solc_mm+'>' from (select solc_yyyy, solc_mm, min(solc_jd) as first, max(solc_jd) as last from 한국천문 group by SOLC_YYYY, SOLC_MM ) a, (select solc_yyyy, solc_mm,  min(solc_jd) as first, max(solc_jd) as last from 한국천문 group by SOLC_YYYY, SOLC_MM ) b where a.first=b.last+1

	--year
		--year class year
			select distinct '<https://astro.kasi.re.kr/time#solcYear'+solc_yyyy+'>', '<http://www.w3.org/2002/07/owl#class>', '<http://dh.aks.ac.kr/ontologies/dhtime#Year>' from 한국천문
		--year label
			select distinct '<https://astro.kasi.re.kr/time#solcYear'+solc_yyyy'>', '<http://www.w3.org/2000/01/rdf-schema#label>', '"'+SOLC_YYYY+'"^^xsd:nonNegativeInteger' from 한국천문
		--year hasTRS solcCalander
			select distinct '<https://astro.kasi.re.kr/time#solcYear'+solc_yyyy+'>', '<http://www.w3.org/2006/time#hasTRS>', '<https://astro.kasi.re.kr/time#solc>' from 한국천문
		--year leap
			select distinct '<https://astro.kasi.re.kr/time#solcYear'+solc_yyyy+'>', '<http://dh.aks.ac.kr/ontologies/dhtime#leap>', 
			case when SOLC_LEAP_YYYY='평' then '"0"^^xsd:nonNegativeInteger' else '"1"^^xsd:nonNegativeInteger' end from 한국천문 
		--year has Beginning day
		select distinct '<https://astro.kasi.re.kr/time#solcYear'+solc_yyyy+'>', '<http://www.w3.org/2006/time#hasBeginning>','<https://astro.kasi.re.kr/time#solcDay'+solc_yyyy+min(solc_mm)+min(SOLC_DD)+'>' from 한국천문 group by SOLC_YYYY
		--year hasEnd day
		select distinct '<https://astro.kasi.re.kr/time#solcYear'+solc_yyyy+'>', '<http://www.w3.org/2006/time#hasBeginning>','<https://astro.kasi.re.kr/time#solcDay'+solc_yyyy+max(solc_mm)+max(SOLC_DD)+'>' from 한국천문 group by SOLC_YYYY
		--year inside day
		select distinct '<https://astro.kasi.re.kr/time#solcYear'+solc_yyyy+'>', '<http://www.w3.org/2006/time#inside>','<https://astro.kasi.re.kr/time#solcDay'+solc_yyyy+solc_mm+SOLC_DD+'>' from 한국천문
		--year hasBeginning month
		select distinct '<https://astro.kasi.re.kr/time#solcYear'+solc_yyyy+'>', '<http://www.w3.org/2006/time#hasBeginning>','<https://astro.kasi.re.kr/time#solcMon'+solc_yyyy+min(solc_mm)+'>' from 한국천문 group by SOLC_YYYY
		--year hasEnd month
		select distinct '<https://astro.kasi.re.kr/time#solcYear'+solc_yyyy+'>', '<http://www.w3.org/2006/time#hasEnd>','<https://astro.kasi.re.kr/time#solcMon'+solc_yyyy+max(solc_mm)+'>' from 한국천문 group by SOLC_YYYY
		--year inside month (inverse of month)
		select distinct '<https://astro.kasi.re.kr/time#solcYear'+solc_yyyy+'>', '<http://www.w3.org/2006/time#inside>','<https://astro.kasi.re.kr/time#solcMon'+solc_yyyy+solc_mm+'>' from 한국천문 group by SOLC_mm, SOLC_YYYY
		--year before year
		select '<https://astro.kasi.re.kr/time#solcYear'+a.solc_yyyy+'>', '<http://www.w3.org/2006/time#before>', '<https://astro.kasi.re.kr/time#solcYear'+b.solc_yyyy+'>' from (select solc_yyyy, min(solc_jd) as first, max(solc_jd) as last from 한국천문 group by SOLC_YYYY ) a,  (select solc_yyyy, min(solc_jd) as first, max(solc_jd) as last from 한국천문 group by SOLC_YYYY ) b where a.last=b.first-1
		--year after year
		select '<https://astro.kasi.re.kr/time#solcYear'+a.solc_yyyy+'>', '<http://www.w3.org/2006/time#after>', '<https://astro.kasi.re.kr/time#solcYear'+b.solc_yyyy+'>' from (select solc_yyyy, min(solc_jd) as first, max(solc_jd) as last from 한국천문 group by SOLC_YYYY ) a,  (select solc_yyyy, min(solc_jd) as first, max(solc_jd) as last from 한국천문 group by SOLC_YYYY ) b where a.first=b.last+1
		--year days
		select distinct '<https://astro.kasi.re.kr/time#solcMon'+solc_yyyy+'>', '<http://www.w3.org/2006/time#days>','"'+cast(count(*) as nvarchar)+'"^^xsd:nonNegativeInteger' from 한국천문 group by SOLC_YYYY
		
 --lunc
		--day class day
		select '<https://astro.kasi.re.kr/time#luncDay'+lunc_yyyy+lunc_mm+lunc_DD+'>', '<http://www.w3.org/2002/07/owl#class>', '<http://dh.aks.ac.kr/ontologies/dhtime#Day>' from 한국천문
		--day ganzhi
		select '<https://astro.kasi.re.kr/time#luncDay'+lunc_yyyy+lunc_mm+lunc_DD+'>', '<http://dh.aks.ac.kr/ontologies/dhtime#ganzhi>', '"'+substring(LUNC_ILJIN,0,CHARINDEX ('(',LUNC_ILJIN))+'"@ko' from 한국천문
		select '<https://astro.kasi.re.kr/time#luncDay'+lunc_yyyy+lunc_mm+lunc_DD+'>', '<http://dh.aks.ac.kr/ontologies/dhtime#ganzhi>', '"'+substring(LUNC_ILJIN,4,2)+'"@hanja' from 한국천문 
		--day label 
		select '<https://astro.kasi.re.kr/time#luncDay'+lunc_yyyy+lunc_mm+lunc_DD+'>', '<http://www.w3.org/2000/01/rdf-schema#label>', '"'+LUNC_DD+'"^^xsd:nonNegativeInteger' from 한국천문
		--day hasTRS Lunc
		select distinct '<https://astro.kasi.re.kr/time#luncDay'+lunc_yyyy+lunc_mm+lunc_DD+'>', '<http://www.w3.org/2006/time#hasTRS>', '<https://astro.kasi.re.kr/time#lunc>' from 한국천문
		--day before day
		select '<https://astro.kasi.re.kr/time#luncDay'+a.lunc_yyyy+a.lunc_mm+a.lunc_DD+'>', '<http://www.w3.org/2006/time#before>', '<https://astro.kasi.re.kr/time#luncDay'+b.lunc_yyyy+b.lunc_mm+b.lunc_DD+'>' from (select lunc_yyyy, lunc_mm, lunc_DD, solc_jd from 한국천문) a, (select lunc_yyyy, lunc_mm, lunc_DD, solc_jd from 한국천문) b  where a.SOLC_JD+1=b.SOLC_JD
		--day after day
		select '<https://astro.kasi.re.kr/time#luncDay'+a.lunc_yyyy+a.lunc_mm+a.lunc_DD+'>', '<http://www.w3.org/2006/time#after>', '<https://astro.kasi.re.kr/time#luncDay'+b.lunc_yyyy+b.lunc_mm+b.lunc_DD+'>' from (select lunc_yyyy, lunc_mm, lunc_DD, solc_jd from 한국천문) a, (select lunc_yyyy, lunc_mm, lunc_DD, solc_jd from 한국천문) b  where a.SOLC_JD=b.SOLC_JD-1

		--month
		--month class month
		select distinct '<https://astro.kasi.re.kr/time#luncMon'+lunc_yyyy+lunc_mm+'>', '<http://www.w3.org/2002/07/owl#class>', '<http://dh.aks.ac.kr/ontologies/dhtime#Month>' from 한국천문
		--month label
		select distinct '<https://astro.kasi.re.kr/time#luncMon'+lunc_yyyy+lunc_mm+'>', '<http://www.w3.org/2000/01/rdf-schema#label>', '"'+lunc_mm+'"^^xsd:nonNegativeInteger' from 한국천문
		--month ganzhi
		select distinct '<https://astro.kasi.re.kr/time#luncDay'+lunc_yyyy+lunc_mm+'>', '<http://dh.aks.ac.kr/ontologies/dhtime#ganzhi>', '"'+substring(LUNC_WLGN,0,CHARINDEX ('(',LUNC_ILJIN))+'"@ko' from 한국천문 where LUNC_WLGN <> ''
		select distinct '<https://astro.kasi.re.kr/time#luncDay'+lunc_yyyy+lunc_mm+'>', '<http://dh.aks.ac.kr/ontologies/dhtime#ganzhi>', '"'+substring(LUNC_WLGN,4,2)+'"@hanja' from 한국천문 where LUNC_WLGN <> ''
		--month hasTRS luncCalander
		select distinct '<https://astro.kasi.re.kr/time#luncMon'+lunc_yyyy+lunc_mm+'>', '<http://www.w3.org/2006/time#hasTRS>', '<https://astro.kasi.re.kr/time#lunc>' from 한국천문
		--month hasBeginning
		select distinct '<https://astro.kasi.re.kr/time#luncMon'+lunc_yyyy+lunc_mm+'>', '<http://www.w3.org/2006/time#hasBeginning>','<https://astro.kasi.re.kr/time#luncDay'+lunc_yyyy+lunc_mm+min(lunc_DD)+'>' from 한국천문 group by lunc_YYYY, lunc_MM
		--month hasEnd
		select distinct '<https://astro.kasi.re.kr/time#luncMon'+lunc_yyyy+lunc_mm+'>', '<http://www.w3.org/2006/time#hasEnd>','<https://astro.kasi.re.kr/time#luncDay'+lunc_yyyy+lunc_mm+max(lunc_DD)+'>' from 한국천문 group by lunc_YYYY, lunc_MM
		--month days 월일수
			select distinct '<https://astro.kasi.re.kr/time#luncMon'+lunc_yyyy+lunc_mm+'>', '<http://www.w3.org/2006/time#days>','"'+cast(count(*) as nvarchar)+'"^^xsd:nonNegativeInteger' from 한국천문 group by lunc_YYYY, lunc_MM 
		--month inside day (inverse of month)
		select distinct '<https://astro.kasi.re.kr/time#luncMon'+lunc_yyyy+lunc_mm+'>', '<http://www.w3.org/2006/time#inside>','<https://astro.kasi.re.kr/time#luncDay'+lunc_yyyy+lunc_mm+lunc_DD+'>' from 한국천문 
		--month before month
		select '<https://astro.kasi.re.kr/time#luncMon'+a.lunc_yyyy+a.lunc_mm+'>', '<http://www.w3.org/2006/time#before>', '<https://astro.kasi.re.kr/time#luncDay'+b.lunc_yyyy+b.lunc_mm+'>' from (select lunc_yyyy, lunc_mm, min(solc_jd) as first, max(solc_jd) as last from 한국천문 group by lunc_YYYY, lunc_MM ) a, (select lunc_yyyy, lunc_mm,  min(solc_jd) as first, max(solc_jd) as last from 한국천문 group by lunc_YYYY, lunc_MM ) b where a.last=b.first-1
		--month after month
		select '<https://astro.kasi.re.kr/time#luncMon'+a.lunc_yyyy+a.lunc_mm+'>', '<http://www.w3.org/2006/time#after>', '<https://astro.kasi.re.kr/time#luncDay'+b.lunc_yyyy+b.lunc_mm+'>' from (select lunc_yyyy, lunc_mm, min(solc_jd) as first, max(solc_jd) as last from 한국천문 group by lunc_YYYY, lunc_MM ) a, (select lunc_yyyy, lunc_mm,  min(solc_jd) as first, max(solc_jd) as last from 한국천문 group by lunc_YYYY, lunc_MM ) b where a.first=b.last+1
		--month leap
			select distinct '<https://astro.kasi.re.kr/time#luncMon'+lunc_yyyy+lunc_mm+'>', '<http://dh.aks.ac.kr/ontologies/dhtime#leap>', 
			case when LUNC_LEAP_MM='평' then '"0"^^xsd:nonNegativeInteger' else '"1"^^xsd:nonNegativeInteger' end from 한국천문 

--year
		--year class year
			select distinct '<https://astro.kasi.re.kr/time#luncYear'+lunc_yyyy+'>', '<http://www.w3.org/2002/07/owl#class>', '<http://dh.aks.ac.kr/ontologies/dhtime#Year>' from 한국천문
		--year label
			select distinct '<https://astro.kasi.re.kr/time#luncYear'+lunc_yyyy'>', '<http://www.w3.org/2000/01/rdf-schema#label>', '"'+lunc_YYYY+'"^^xsd:nonNegativeInteger' from 한국천문
		--year hasTRS luncCalander
			select distinct '<https://astro.kasi.re.kr/time#luncYear'+lunc_yyyy+'>', '<http://www.w3.org/2006/time#hasTRS>', '<https://astro.kasi.re.kr/time#lunc>' from 한국천문
		--year has Beginning day
		select distinct '<https://astro.kasi.re.kr/time#luncYear'+lunc_yyyy+'>', '<http://www.w3.org/2006/time#hasBeginning>','<https://astro.kasi.re.kr/time#luncDay'+lunc_yyyy+min(lunc_mm)+min(lunc_DD)+'>' from 한국천문 group by lunc_YYYY
		--year hasEnd day
		select distinct '<https://astro.kasi.re.kr/time#luncYear'+lunc_yyyy+'>', '<http://www.w3.org/2006/time#hasBeginning>','<https://astro.kasi.re.kr/time#luncDay'+lunc_yyyy+max(lunc_mm)+max(lunc_DD)+'>' from 한국천문 group by lunc_YYYY
		--year inside day
		select distinct '<https://astro.kasi.re.kr/time#luncYear'+lunc_yyyy+'>', '<http://www.w3.org/2006/time#inside>','<https://astro.kasi.re.kr/time#luncDay'+lunc_yyyy+lunc_mm+lunc_DD+'>' from 한국천문
		--year hasBeginning month
		select distinct '<https://astro.kasi.re.kr/time#luncYear'+lunc_yyyy+'>', '<http://www.w3.org/2006/time#hasBeginning>','<https://astro.kasi.re.kr/time#luncMon'+lunc_yyyy+min(lunc_mm)+'>' from 한국천문 group by lunc_YYYY
		--year hasEnd month
		select distinct '<https://astro.kasi.re.kr/time#luncYear'+lunc_yyyy+'>', '<http://www.w3.org/2006/time#hasEnd>','<https://astro.kasi.re.kr/time#luncMon'+lunc_yyyy+max(lunc_mm)+'>' from 한국천문 group by lunc_YYYY
		--year inside month (inverse of month)
		select distinct '<https://astro.kasi.re.kr/time#luncYear'+lunc_yyyy+'>', '<http://www.w3.org/2006/time#inside>','<https://astro.kasi.re.kr/time#luncMon'+lunc_yyyy+lunc_mm+'>' from 한국천문 group by lunc_mm, lunc_YYYY
		--year before year
		select '<https://astro.kasi.re.kr/time#luncYear'+a.lunc_yyyy+'>', '<http://www.w3.org/2006/time#before>', '<https://astro.kasi.re.kr/time#luncYear'+b.lunc_yyyy+'>' from (select lunc_yyyy, min(SOLC_JD) as first, max(SOLC_JD) as last from 한국천문 group by lunc_YYYY ) a,  (select lunc_yyyy, min(SOLC_JD) as first, max(SOLC_JD) as last from 한국천문 group by lunc_YYYY ) b where a.last=b.first-1
		--year after year
		select '<https://astro.kasi.re.kr/time#luncYear'+a.lunc_yyyy+'>', '<http://www.w3.org/2006/time#after>', '<https://astro.kasi.re.kr/time#luncYear'+b.lunc_yyyy+'>' from (select lunc_yyyy, min(SOLC_JD) as first, max(SOLC_JD) as last from 한국천문 group by lunc_YYYY ) a,  (select lunc_yyyy, min(SOLC_JD) as first, max(SOLC_JD) as last from 한국천문 group by lunc_YYYY ) b where a.first=b.last+1
		--year days
		select distinct '<https://astro.kasi.re.kr/time#luncMon'+lunc_yyyy+'>', '<http://www.w3.org/2006/time#days>','"'+cast(count(*) as nvarchar)+'"^^xsd:nonNegativeInteger' from 한국천문 group by lunc_YYYY
원본 주소 "https://dh.aks.ac.kr/Edu/wiki/index.php?title=KASI_Convert_sql&oldid=77448"