Enjoy Programming

[QUERY] test 쿼리 임시저장 본문

MySQL

[QUERY] test 쿼리 임시저장

LEETAEEON 2023. 5. 11. 09:18 
const routeList = Array.from(Object.keys(bi52routeInfo));

			const queryList = routeList.map((route) => {
				const query = `select (SELECT JSON_OBJECT('route', '${
					route.split('_')[1]
				}_20ft', 'data', JSON_ARRAYAGG( JSON_OBJECT('date', dates_formatted,'value', value))) AS '${
					route.split('_')[1]
				}_20ft' FROM (SELECT DATE_FORMAT(dates, '%Y-%m-%d') AS dates_formatted, AVG(20dc) AS value, route FROM bi52_lcr WHERE dates between DATE_SUB(NOW(),INTERVAL 30 DAY) AND NOW() and route = '${route}' GROUP BY dates_formatted, route) AS subquery GROUP BY route) as '${
					route.split('_')[1]
				}_20ft', (SELECT JSON_OBJECT( 'route', '${
					route.split('_')[1]
				}_40ft','data', JSON_ARRAYAGG(JSON_OBJECT('date', dates_formatted,'value', value))) AS '${
					route.split('_')[1]
				}_40ft' FROM (SELECT DATE_FORMAT(dates, '%Y-%m-%d') AS dates_formatted, AVG(40hc) AS value, route FROM bi52_lcr WHERE dates between DATE_SUB(NOW(),INTERVAL 30 DAY) AND NOW() and route = '${route}' GROUP BY dates_formatted, route) AS subquery GROUP BY route) as '${
					route.split('_')[1]
				}_40ft';`;
				return query;
			});




const routeList = Array.from(Object.keys(bi52routeInfo));

			const queryList = routeList.map((route) => {
				const query = `(SELECT JSON_OBJECT('route', '${
					route.split('_')[1]
				}_20ft', 'data', JSON_ARRAYAGG( JSON_OBJECT('date', dates_formatted,'value', value))) AS '${
					route.split('_')[1]
				}_20ft' FROM (SELECT DATE_FORMAT(dates, '%Y-%m-%d') AS dates_formatted, AVG(20dc) AS value, route FROM bi52_lcr WHERE dates between DATE_SUB(NOW(),INTERVAL 30 DAY) AND NOW() and route = '${route}' GROUP BY dates_formatted, route) AS subquery GROUP BY route) as '${
					route.split('_')[1]
				}_20ft', (SELECT JSON_OBJECT( 'route', '${
					route.split('_')[1]
				}_40ft','data', JSON_ARRAYAGG(JSON_OBJECT('date', dates_formatted,'value', value))) AS '${
					route.split('_')[1]
				}_40ft' FROM (SELECT DATE_FORMAT(dates, '%Y-%m-%d') AS dates_formatted, AVG(40hc) AS value, route FROM bi52_lcr WHERE dates between DATE_SUB(NOW(),INTERVAL 30 DAY) AND NOW() and route = '${route}' GROUP BY dates_formatted, route) AS subquery GROUP BY route) as '${
					route.split('_')[1]
				}_40ft',`;
				return query;
			}).join()
				.replace(/,,/g, ',');
			const lastIndex = queryList.length - 1;
			const query = `select ${queryList.substring(0, lastIndex)};`;
			// console.log(query);
			const result = await getManager('mysql').query(query);
	const newQuery = `
			SELECT '20ft' AS type, route, JSON_ARRAYAGG(JSON_OBJECT('date', dates_formatted, 'value', 20value)) AS data
				FROM (
				SELECT
					DATE_FORMAT(dates, '%Y-%m-%d') AS dates_formatted, AVG(20dc) AS 20value, route
					FROM
							bi52_lcr
					WHERE
					dates between DATE_SUB(NOW(),INTERVAL 5 MONTH) AND NOW()  and
							route IN (
								'busan_toronto',
						'busan_riyadh',
						'busan_apapa',
						'busan_fremantle',
						'busan_barcelona',
						'busan_laspezia',
						'busan_montreal',
						'busan_miami',
						'busan_vancouver',
						'busan_newyork',
						'busan_savannah',
						'busan_longbeach',
						'busan_chicago',
						'busan_losangeles',
						'busan_oakland',
						'busan_puertoquetzal',
						'busan_buenaventura',
						'busan_izmit',
						'busan_venice',
						'busan_alexandria',
						'busan_kolkata',
						'busan_southampton',
						'busan_jakarta',
						'busan_mobile',
						'busan_vladivostok',
						'busan_huangpu',
						'busan_sohar',
						'busan_tokyo',
						'busan_nagoya',
						'busan_yokohama',
						'busan_osaka',
						'busan_kobe',
						'busan_dalian',
						'busan_xingang',
						'busan_qingdao',
						'busan_xiamen',
						'busan_ningbo',
						'busan_shekou',
						'busan_hongkong',
						'busan_shanghai',
						'busan_auckland',
						'busan_brisbane',
						'busan_sydney',
						'busan_adelaide',
						'busan_melbourne',
						'busan_buenosaires',
						'busan_santos',
						'busan_valparaiso',
						'busan_callao',
						'busan_manzanillo',
						'busan_djibouti',
						'busan_mombasa',
						'busan_daressalaam',
						'busan_durban',
						'busan_luanda',
						'busan_dakar',
						'busan_tema',
						'busan_lagos',
						'busan_dammam',
						'busan_hamad',
						'busan_jebelali',
						'busan_keelung',
						'busan_kaohsiung',
						'busan_chittagong',
						'busan_manila',
						'busan_yangon',
						'busan_portklang',
						'busan_laemchabang',
						'busan_bangkok',
						'busan_singapore',
						'busan_haiphong',
						'busan_hochiminh',
						'busan_constanta',
						'busan_mundra',
						'busan_karachi',
						'busan_chennai',
						'busan_nhavasheva',
						'busan_lehavre',
						'busan_antwerp',
						'busan_hamburg',
						'busan_rotterdam',
						'busan_jeddah',
						'busan_aqaba',
						'busan_genoa',
						'busan_valencia',
						'busan_istanbul',
						'busan_houston',
						'busan_tacoma',
						'busan_seattle',
						'busan_portland',
						'busan_wellington',
						'busan_napier',
						'busan_tauranga',
						'busan_lyttelton',
						'busan_charleston',
						'busan_baltimore',
						'busan_neworleans'
							)
					GROUP BY dates_formatted, route) t
					GROUP BY
							route
					UNION ALL
			SELECT '40ft' AS type, route, JSON_ARRAYAGG(JSON_OBJECT('date', dates_formatted, 'value', 40value)) AS data
					FROM (
							SELECT
									DATE_FORMAT(dates, '%Y-%m-%d') AS dates_formatted,
									AVG(40hc) AS 40value,
									route
							FROM
									bi52_lcr
							WHERE
							dates between DATE_SUB(NOW(),INTERVAL 5 MONTH) AND NOW()  and
									route IN (
										'busan_toronto',
						'busan_riyadh',
						'busan_apapa',
						'busan_fremantle',
						'busan_barcelona',
						'busan_laspezia',
						'busan_montreal',
						'busan_miami',
						'busan_vancouver',
						'busan_newyork',
						'busan_savannah',
						'busan_longbeach',
						'busan_chicago',
						'busan_losangeles',
						'busan_oakland',
						'busan_puertoquetzal',
						'busan_buenaventura',
						'busan_izmit',
						'busan_venice',
						'busan_alexandria',
						'busan_kolkata',
						'busan_southampton',
						'busan_jakarta',
						'busan_mobile',
						'busan_vladivostok',
						'busan_huangpu',
						'busan_sohar',
						'busan_tokyo',
						'busan_nagoya',
						'busan_yokohama',
						'busan_osaka',
						'busan_kobe',
						'busan_dalian',
						'busan_xingang',
						'busan_qingdao',
						'busan_xiamen',
						'busan_ningbo',
						'busan_shekou',
						'busan_hongkong',
						'busan_shanghai',
						'busan_auckland',
						'busan_brisbane',
						'busan_sydney',
						'busan_adelaide',
						'busan_melbourne',
						'busan_buenosaires',
						'busan_santos',
						'busan_valparaiso',
						'busan_callao',
						'busan_manzanillo',
						'busan_djibouti',
						'busan_mombasa',
						'busan_daressalaam',
						'busan_durban',
						'busan_luanda',
						'busan_dakar',
						'busan_tema',
						'busan_lagos',
						'busan_dammam',
						'busan_hamad',
						'busan_jebelali',
						'busan_keelung',
						'busan_kaohsiung',
						'busan_chittagong',
						'busan_manila',
						'busan_yangon',
						'busan_portklang',
						'busan_laemchabang',
						'busan_bangkok',
						'busan_singapore',
						'busan_haiphong',
						'busan_hochiminh',
						'busan_constanta',
						'busan_mundra',
						'busan_karachi',
						'busan_chennai',
						'busan_nhavasheva',
						'busan_lehavre',
						'busan_antwerp',
						'busan_hamburg',
						'busan_rotterdam',
						'busan_jeddah',
						'busan_aqaba',
						'busan_genoa',
						'busan_valencia',
						'busan_istanbul',
						'busan_houston',
						'busan_tacoma',
						'busan_seattle',
						'busan_portland',
						'busan_wellington',
						'busan_napier',
						'busan_tauranga',
						'busan_lyttelton',
						'busan_charleston',
						'busan_baltimore',
						'busan_neworleans'
						)
					GROUP BY dates_formatted, route) t
					GROUP BY route;
		`;



최종 쿼리 

SELECT
					JSON_OBJECT("type", "20ft", 'route', route, 'data', JSON_ARRAYAGG( JSON_OBJECT('date', t.dates_formatted, 'value', t.20value))),
					JSON_OBJECT("type", "40ft", 'route', route, 'data', JSON_ARRAYAGG( JSON_OBJECT('date', t.dates_formatted, 'value', t.40value)))
					FROM (
					SELECT DATE_FORMAT(dates, '%Y-%m-%d') AS dates_formatted, AVG(20dc) AS 20value, AVG(40hc) AS 40value, route
					FROM bi52_lcr
					WHERE
					dates between DATE_SUB(NOW(),INTERVAL 6 MONTH) AND NOW()  and
					route IN (
						'busan_toronto',
						'busan_riyadh',
						'busan_apapa',
						'busan_fremantle',
						'busan_barcelona',
						'busan_laspezia',
						'busan_montreal',
						'busan_miami',
						'busan_vancouver',
						'busan_newyork',
						'busan_savannah',
						'busan_longbeach',
						'busan_chicago',
						'busan_losangeles',
						'busan_oakland',
						'busan_puertoquetzal',
						'busan_buenaventura',
						'busan_izmit',
						'busan_venice',
						'busan_alexandria',
						'busan_kolkata',
						'busan_southampton',
						'busan_jakarta',
						'busan_mobile',
						'busan_vladivostok',
						'busan_huangpu',
						'busan_sohar',
						'busan_tokyo',
						'busan_nagoya',
						'busan_yokohama',
						'busan_osaka',
						'busan_kobe',
						'busan_dalian',
						'busan_xingang',
						'busan_qingdao',
						'busan_xiamen',
						'busan_ningbo',
						'busan_shekou',
						'busan_hongkong',
						'busan_shanghai',
						'busan_auckland',
						'busan_brisbane',
						'busan_sydney',
						'busan_adelaide',
						'busan_melbourne',
						'busan_buenosaires',
						'busan_santos',
						'busan_valparaiso',
						'busan_callao',
						'busan_manzanillo',
						'busan_djibouti',
						'busan_mombasa',
						'busan_daressalaam',
						'busan_durban',
						'busan_luanda',
						'busan_dakar',
						'busan_tema',
						'busan_lagos',
						'busan_dammam',
						'busan_hamad',
						'busan_jebelali',
						'busan_keelung',
						'busan_kaohsiung',
						'busan_chittagong',
						'busan_manila',
						'busan_yangon',
						'busan_portklang',
						'busan_laemchabang',
						'busan_bangkok',
						'busan_singapore',
						'busan_haiphong',
						'busan_hochiminh',
						'busan_constanta',
						'busan_mundra',
						'busan_karachi',
						'busan_chennai',
						'busan_nhavasheva',
						'busan_lehavre',
						'busan_antwerp',
						'busan_hamburg',
						'busan_rotterdam',
						'busan_jeddah',
						'busan_aqaba',
						'busan_genoa',
						'busan_valencia',
						'busan_istanbul',
						'busan_houston',
						'busan_tacoma',
						'busan_seattle',
						'busan_portland',
						'busan_wellington',
						'busan_napier',
						'busan_tauranga',
						'busan_lyttelton',
						'busan_charleston',
						'busan_baltimore',
						'busan_neworleans'
					)
					GROUP BY dates_formatted, route
			) t
			GROUP BY route, '20dc', '40hc';

'MySQL' 카테고리의 다른 글

MySQL - warning  (0) 2022.01.21
'MySQL' Related Articles more