Register
Results 1 to 5 of 5
  1. #1

    [Suggestion] per AH statistics

    Hi,

    I thought maybe some general per AH statistics would be nice?
    Total auctions, Sum of all bids, Sum of all buyouts, Items posted per hour?

    I am running some locally on my scans of a EU server and I get really interesting figures:

    Code:
    mysql> SELECT raw.sid, COUNT(raw.auc) AS num, scans.ts FROM raw, scans WHERE raw.sid = scans.sid AND NOT EXISTS (SELECT * FROM raw AS r2 WHERE r2.auc = raw.auc AND r2.sid = raw.sid - 1) GROUP BY raw.sid;
    +------+-------+---------------------+
    | sid  | num   | ts                  |
    +------+-------+---------------------+
    |    3 | 26617 | 2011-02-21 09:55:49 |
    |    4 |  1373 | 2011-02-21 10:59:24 |
    |    5 |  1868 | 2011-02-21 12:06:28 |
    |    6 |  1501 | 2011-02-21 13:06:01 |
    |    7 |   976 | 2011-02-21 13:58:17 |
    |    8 |  1396 | 2011-02-21 15:01:27 |
    |    9 |  1719 | 2011-02-21 16:17:20 |
    +------+-------+---------------------+
    7 rows in set (2.29 sec)
    Code:
    mysql> SELECT raw.sid, SUM(raw.buy) / 10000 AS sbuy, SUM(raw.bid) / 10000 AS sbid, scans.ts FROM raw, scans WHERE raw.sid = scans.sid GROUP BY raw.sid;
    +------+--------------+--------------+---------------------+
    | sid  | sbuy         | sbid         | ts                  |
    +------+--------------+--------------+---------------------+
    |    3 | 3737471.2717 | 3392290.3116 | 2011-02-21 09:55:49 |
    |    4 | 3732810.4481 | 3386490.8833 | 2011-02-21 10:59:24 |
    |    5 | 3674968.4099 | 3349674.8575 | 2011-02-21 12:06:28 |
    |    6 | 3861021.1016 | 3527223.8134 | 2011-02-21 13:06:01 |
    |    7 | 3884958.7615 | 3542748.4545 | 2011-02-21 13:58:17 |
    |    8 | 3794055.8184 | 3460405.6495 | 2011-02-21 15:01:27 |
    |    9 | 3730972.7328 | 3398188.4704 | 2011-02-21 16:17:20 |
    +------+--------------+--------------+---------------------+
    7 rows in set (0.54 sec)

  2. #2
    Erorus's Avatar
    Posts
    1,404
    Reputation
    114
    Tagged in
    677 Posts
    Add to this user's reputation
    I started working on a realm size motion chart, but haven't yet got any good copy for it. Still, pretty moving circles...

    When considering what to put on the site, there's useful stats, and there's fluff. This tricky bugger straddles the line.

  3. #3
    I like the realm size charts :-) However in my opinion statistics such as the sum of all bids or the sum of all buyout values would be interesting to some.

    Oh and I just thought of another query that might be of interest to some: For all auctions of a seller find all auctions by others with a lower price per unit buyout. Not sure if your server can handle too much of those but they look interesting to me

    After adding an index for itemids this works reasonably well on my DB

    Code:
    mysql> SELECT i.name, MIN(r.ppuBuy), SUM(r.quan), MIN(r2.ppuBuy) FROM raw AS r2, raw AS r, items AS i WHERE r2.sid = 14 AND r2.seller = 'Lunathyr' AND r.sid = r2.sid AND r.iid = r2.iid AND i.id = r2.iid AND r.seller != 'Lunathyr' AND r.ppuBuy < r2.ppuBuy GROUP BY r2.iid; 
    +---------------------------+---------------+-------------+----------------+
    | name                      | MIN(r.ppuBuy) | SUM(r.quan) | MIN(r2.ppuBuy) |
    +---------------------------+---------------+-------------+----------------+
    | Lesser Magic Essence      |          4550 |         264 |          33330 |
    | Greater Magic Essence     |         48533 |         192 |          99990 |
    | Strange Dust              |          8750 |         396 |           8995 |
    | Soul Dust                 |         10000 |        2090 |          19995 |
    | Lesser Mystic Essence     |         32999 |         108 |          33330 |
    | Greater Mystic Essence    |         60200 |         210 |          66660 |
    | Vision Dust               |         16000 |        4152 |          39995 |
    | Lesser Nether Essence     |         59500 |         252 |         199990 |
    | Greater Nether Essence    |        180000 |        2520 |         599990 |
    | Large Brilliant Shard     |         82000 |         774 |         199990 |
    | Lesser Eternal Essence    |         90000 |          21 |         233330 |
    | Greater Eternal Essence   |        299999 |         338 |         699990 |
    | Illusion Dust             |         47057 |         969 |         299995 |
    | Arcane Dust               |             0 |        4284 |          49995 |
    | Greater Planar Essence    |         99898 |        3924 |         599990 |
    | Lesser Planar Essence     |         58499 |         936 |         199990 |
    | Large Prismatic Shard     |          9261 |         576 |          33330 |
    | Void Crystal              |         18800 |         684 |          99990 |
    | Dream Shard               |             0 |        1728 |          99990 |
    | Infinite Dust             |             0 |        6840 |          59995 |
    | Greater Cosmic Essence    |             0 |         348 |          66660 |
    | Lesser Cosmic Essence     |         21000 |         561 |          22220 |
    | Abyss Crystal             |        329000 |         774 |         399990 |
    | Hypnotic Dust             |         39800 |       15765 |          99990 |
    | Lesser Celestial Essence  |        259999 |         702 |         333330 |
    | Greater Celestial Essence |        520000 |        1216 |         999990 |
    | Heavenly Shard            |         91500 |        1725 |         555550 |
    +---------------------------+---------------+-------------+----------------+
    27 rows in set (0.90 sec)
    There is a join on the item name / item id, and all auctions on any item cheaper than Lunathyr has posted it. After that I group them on a per item basis and show the min price Lunathyr has and the min price others have and the amount others offer. (Lunathyr has a ton of enchanting mats for the maximum price so this is a nice worst case query)

    But this might be interesting, maybe as notification limited to a few items to get notified if you are undercut?
    Last edited by Amandria; February 22nd, 2011 at 06:24 AM.

  4. #4
    Erorus's Avatar
    Posts
    1,404
    Reputation
    114
    Tagged in
    677 Posts
    Add to this user's reputation
    Quote Originally Posted by Amandria View Post
    I like the realm size charts :-) However in my opinion statistics such as the sum of all bids or the sum of all buyout values would be interesting to some.
    All it takes is one person to post something for a ridiculous 100k+ bid/buyout for those numbers to become grossly inaccurate. Not all values received are legitimate.

    Quote Originally Posted by Amandria View Post
    Oh and I just thought of another query that might be of interest to some: For all auctions of a seller find all auctions by others with a lower price per unit buyout.
    The seller page already shows you the quantity of items currently available at a lower price, listed in the "Cheaper" column of the Current Items list at the end.

  5. #5
    Quote Originally Posted by Erorus View Post
    The seller page already shows you the quantity of items currently available at a lower price, listed in the "Cheaper" column of the Current Items list at the end.
    Sorry, ma fault! Did not see that column.

 

 

Similar Threads

  1. Category Suggestions
    By Nerfarious in forum Official Forum of The Undermine Journal
    Replies: 18
    Last Post: May 30th, 2011, 11:53 AM
  2. New Statistics: US Alliance Market Price & US Alliance Mean
    By KaliopeLlane in forum Official Forum of The Undermine Journal
    Replies: 1
    Last Post: March 16th, 2011, 04:07 PM
  3. Is this possible? If not, a suggestion!
    By Faid in forum General TradeSkillMaster Discussion
    Replies: 5
    Last Post: March 11th, 2011, 11:40 AM
  4. Battleground: Horde/Alliance Win Ratio Statistics
    By Sinshroud in forum Off Topic
    Replies: 8
    Last Post: February 22nd, 2011, 07:37 PM