K5wDLpB3

1CREATE DEFINER=`root`@`localhost` PROCEDURE `GETMARKETDATA`()
2BEGIN
3# DROP TEMP TABLES
4DROP TEMPORARY TABLE IF EXISTS templocalization;
5DROP TEMPORARY TABLE IF EXISTS tempworld;
6DROP TEMPORARY TABLE IF EXISTS tempmarket;
7
8# CREATE ITEM TEMP TABLE
9CREATE TEMPORARY TABLE IF NOT EXISTS templocalization AS
10(SELECT 
11    albion.localization.seg0,
12    albion.localization.segUID
13FROM albion.localization
14);
15#################
16# CREATE LOCATION TEMP TABLE
17CREATE TEMPORARY TABLE IF NOT EXISTS tempworld AS
18(SELECT 
19    CAST(albion.world.ITEM_ID AS UNSIGNED) AS ITEM_ID,
20    albion.world.LOCNAME
21FROM albion.world
22where ITEM_ID REGEXP '^[0-9]+$');
23#################
24
25CREATE TEMPORARY TABLE IF NOT EXISTS tempmarket AS
26(SELECT
27       templocalization.seg0, # Name of actual Item
28       market_orders.price,
29       market_orders.location,
30       market_orders.auction_type,
31       market_orders.created_at
32FROM market_orders
33INNER JOIN templocalization ON templocalization.segUID = market_orders.item_id);
34#################
35
36Select 
37	tempmarket.seg0 AS 'ITEM',
38    ROUND(avg(tempmarket.price), 0) AS 'PRICE',
39    tempworld.LOCNAME AS 'LOCATION',
40    tempmarket.auction_type AS 'Buy or Sell',
41    DATE_FORMAT(tempmarket.created_at, '%Y-%m-%d %H:00:00') AS 'Listed Time'
42    from tempmarket
43INNER JOIN tempworld ON tempworld.ITEM_ID = tempmarket.location
44GROUP BY hour( tempmarket.created_at ) , day( tempmarket.created_at ), tempmarket.seg0, tempworld.LOCNAME, HOUR(tempmarket.created_at);
45END