List:General Discussion« Previous MessageNext Message »
From:Jan Steinman Date:September 19 2012 4:25am
Subject:Re: Making myself crazy...
View as plain text  
Thanks for your help, Rick.

The solution that seems to be working was to have both the FROM and LEFT JOIN be grouped
subqueries. I realize this is probably not the best way of doing things, but it seems
fast enough at present.

Here is the query, followed by the table definitions, resulting in the following data:
	http://www.EcoReality.org/wiki/2012_gross_productivity

-- (The following query is from a template, in which "{{{1}}}" is replaced by a year, such
as "2012".)
SELECT
  pc AS `Profit Centre`,
  CONCAT('$', FORMAT(prp, 2)) AS Paid,
  CONCAT('$', FORMAT(prt, 2)) AS Trade,
  CONCAT('$', FORMAT(prtot, 2)) AS `Both`
FROM
(SELECT
  tt.`Profit Centre` AS pc,
  mt.Paid / tt.Hours AS prp,
  mt.Trade / tt.Hours AS prt,
  mt.tot / tt.Hours AS prtot
FROM
(SELECT
  SUM(TIMESTAMPDIFF(MINUTE, tl.`in`, tl.`out`)/60) AS Hours,
  pc.ID AS pcid,
  pc.name AS `Profit Centre`
FROM s_timelog tl
LEFT JOIN
  s_project proj ON tl.project_id = proj.id
LEFT JOIN
  s_profit_centre pc ON proj.profit_centre = pc.ID
WHERE proj.private = 'no' AND YEAR(tl.`in`) = '{{{1}}}'
GROUP BY `Profit Centre`) tt
LEFT JOIN
  (SELECT
    SUM(case when sales.Type NOT IN('barter', 'work trade') then `Total` else 0 end) AS
Paid,
    SUM(case when sales.Type IN('barter', 'work trade') then `Total` else 0 end) AS Trade,
    SUM(`Total`) AS tot,
    pc.ID AS pcid,
    pc.name AS `Profit Centre`
  FROM s_product_sales_log sales
  LEFT JOIN
    s_product prod ON sales.Product = prod.ID
  LEFT JOIN
    s_profit_centre pc ON prod.profit_centre = pc.ID
  WHERE YEAR(sales.`Date`) = '{{{1}}}'
  GROUP BY `Profit Centre`) mt
    ON mt.pcid = tt.pcid
WHERE mt.tot != 0) xx

--"s_timelog" and "s_product_sales_log" are the two "ends" of the query, each related by
an intermediate table to the "centre" of the query, "s_profit_centre":
-- s_timelog.project_id --> s_project.id, s_project.profit_centre -->
s_profit_centre.ID
-- s_product_sales_log.Product --> s_product.ID, s_product.profit_centre -->
s_profit_centre.ID

----------------

-- 18,739 rows, growing by ~100 records monthly
CREATE TABLE `s_timelog` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `mw_user_id` int(11) unsigned NOT NULL default '2',
  `in` datetime NOT NULL,
  `out` datetime NOT NULL,
  `project_id` int(11) unsigned NOT NULL,
  `activity_id` int(11) unsigned NOT NULL,
  `accomplishment` varchar(255) character set utf8 NOT NULL,
  `Paid` enum('yes','no') character set utf8 NOT NULL default 'no' COMMENT 'hours that are
compensated',
  `Class_B_qualified` enum('yes','no') character set utf8 NOT NULL default 'no',
  `updated` timestamp NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  KEY `mw_user_id` (`mw_user_id`),
  KEY `in` (`in`),
  KEY `project_id` (`project_id`),
  KEY `activity_id` (`activity_id`),
  KEY `out` (`out`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='EcoReality time log.'
AUTO_INCREMENT=19462 ;
--
-- RELATIONS FOR TABLE `s_timelog`:
--   `user_id`
--       `mw_user` -> `user_id`
--   `mw_user_id`
--       `mw_user` -> `user_id`
--   `project_id`
--       `s_project` -> `id`
--   `activity_id`
--       `s_activity` -> `id`

----------------

-- 143 rows, growing by one or two a month
CREATE TABLE IF NOT EXISTS `s_project` (
  `id` int(6) unsigned NOT NULL auto_increment,
  `profit_centre` int(10) unsigned NOT NULL default '0',
  `profit_centre_amortized` int(3) unsigned default '0' COMMENT 'for capital projects',
  `name` varchar(62) NOT NULL,
  `URL` varchar(255) default NULL,
  `need` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `super_id` int(11) unsigned NOT NULL default '0',
  `type` enum('capital','operations') NOT NULL default 'operations',
  `duration` enum('fixed','ongoing') NOT NULL default 'fixed',
  `status` enum('abandoned','agreed','cancelled','completed','on
hold','ongoing','requested') NOT NULL default 'requested',
  `start` date NOT NULL,
  `end` date NOT NULL,
  `completed` date NOT NULL,
  `depends_on_id` int(6) unsigned NOT NULL default '0',
  `budget_expense` decimal(7,2) NOT NULL,
  `budget_effort` decimal(7,2) NOT NULL,
  `effort_units` enum('minutes','hours','days','weeks','months') NOT NULL default 'hours',
  `steward_id` int(5) unsigned NOT NULL default '0',
  `private` enum('yes','no') NOT NULL default 'no',
  `updated` timestamp NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `notes` text NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `duration` (`duration`),
  KEY `status` (`status`),
  KEY `super_id` (`super_id`,`depends_on_id`,`steward_id`),
  KEY `depends_on_id` (`depends_on_id`,`steward_id`),
  KEY `steward_id` (`steward_id`),
  KEY `profit_centre` (`profit_centre`),
  KEY `profit_centre_amortized` (`profit_centre_amortized`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='EcoReality (and other) projects that
require resources.' AUTO_INCREMENT=145 ;

--
-- RELATIONS FOR TABLE `s_project`:
--   `steward`
--       `mw_user` -> `user_id`
--   `super_id`
--       `s_project` -> `id`
--   `depends_on_id`
--       `s_project` -> `id`
--   `steward_id`
--       `mw_user` -> `user_id`
--   `profit_centre`
--       `s_profit_centre` -> `ID`
--   `profit_centre_amortized`
--       `s_profit_centre` -> `ID`

----------------

-- 12 records, growing by one or two a year
CREATE TABLE IF NOT EXISTS `s_profit_centre` (
  `ID` int(3) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `steward` int(5) unsigned NOT NULL,
  `updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `notes` text NOT NULL,
  PRIMARY KEY  (`ID`),
  KEY `steward` (`steward`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- RELATIONS FOR TABLE `s_profit_centre`:
--   `steward`
--       `mw_user` -> `user_id`


----------------

-- 3,090 rows, growing by a few hundred monthly
CREATE TABLE `s_product_sales_log` (
  `Venue` enum('EcoReality grounds','farm gate','delivered','Tuesday
market','roadside','Saturday market','store','subscription','Yellow House kitchen')
character set utf8 NOT NULL default 'farm gate',
  `Seller` int(11) unsigned NOT NULL default '212',
  `Who` int(11) unsigned NOT NULL default '0',
  `Date` date NOT NULL,
  `Quantity` decimal(8,3) NOT NULL,
  `Unit`
enum('bale','box','bunch','dozen','each','grams','kilograms','liters','ounces','pints','pounds','quarts')
character set utf8 collate utf8_bin NOT NULL default 'each',
  `Total` decimal(8,2) NOT NULL,
  `Type` enum('barter','cash','check','invoice','PayPal','work trade') character set utf8
NOT NULL default 'cash',
  `Product` int(11) unsigned NOT NULL,
  KEY `Product` (`Product`),
  KEY `Date` (`Date`),
  KEY `Seller` (`Seller`),
  KEY `Who_2` (`Who`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- RELATIONS FOR TABLE `s_product_sales_log`:
--   `Seller`
--       `mw_user` -> `user_id`
--   `Who`
--       `mw_user` -> `user_id`
--   `Product`
--       `s_product` -> `ID`

----------------

-- 186 records, growing by ~1 monthly
CREATE TABLE IF NOT EXISTS `s_product` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `super` int(11) unsigned default NULL COMMENT 'generalization',
  `name` varchar(31) character set utf8 NOT NULL,
  `units` enum('kilograms','grams','pounds','ounces','liters','each','cords','bales')
character set utf8 NOT NULL default 'kilograms' COMMENT 'preferred unit',
  `profit_centre` int(3) unsigned NOT NULL default '3',
  `tax_qualified` enum('yes','no') character set utf8 NOT NULL default 'yes',
  `tax_livestock_born` enum('yes','no') character set utf8 NOT NULL default 'no',
  `tax_poultry_egg` enum('yes','no') character set utf8 NOT NULL default 'no',
  `is_value_added` enum('yes','no') character set utf8 NOT NULL default 'no',
  `plant_ID` int(5) unsigned default NULL,
  `description` varchar(255) character set utf8 NOT NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `Name` (`name`),
  KEY `Description` (`description`),
  KEY `is_value_added` (`is_value_added`),
  KEY `profit_centre` (`profit_centre`),
  KEY `super` (`super`),
  KEY `plant_ID` (`plant_ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='list of EcoReality farm
products' AUTO_INCREMENT=186 ;

--
-- RELATIONS FOR TABLE `s_product`:
--   `super`
--       `s_product` -> `ID`
--   `profit_centre`
--       `s_profit_centre` -> `ID`
--   `plant_ID`
--       `s_plants` -> `ID`


On 17 Sep 12, at 16:12, Rick James wrote:

> If the subquery-version is not too slow, live with it.
> 
> If necessary, make your non-grouped SELECT a subquery and apply GROUP BY outside. 
> Thus:
> 
> SELECT ... FROM ( SELECT non-grouped... ) GROUP BY ...
> 
> Could you provide that; we might be able to simplify it.
> Also provide SHOW CREATE TABLE for each table.
> How many rows in each table?  (approx)
> 
>> -----Original Message-----
>> From: Jan Steinman [mailto:Jan@stripped]
>> Sent: Sunday, September 16, 2012 3:45 PM
>> To: mysql@stripped
>> Subject: Making myself crazy...
>> 
>> I'm having trouble figuring out how to make a query. It seems simple,
>> but it's making me crazy right now. Please point out where my thinking
>> is addled...
>> 
>> I have the following (simplified) table structure:
>> 
>> s.timelog --> s.projects --> s.profit_centres
>> 
>> s.product.sales --> s.products --> s.profit_centres
>> 
>> (The arrows refer to many-to-one relationships: each Timelog record
>> refers to a single Project, which is in a single Profit Centre. Each
>> record has an opaque ID referred to by records in the table to its
>> left.)
>> 
>> What I want to do is figure out productivity: sales per hour worked per
>> Profit Centre.
>> 
>> I can do this at a gross level -- without grouping by Profit Centres --
>> with a subquery: simply sum up the Lales and divide by the sum of the
>> labour (Timelog.out - Timelog.in). But I suspect even this can be done
>> without a subquery.
>> 
>> But needing to do two levels of indirection has stymied me: how can I
>> group $/hour by Profit Centre?
>> 
>> You can see the SQL here if you wish:
>> 	http://www.ecoreality.org/wiki/Template:Annual_gross_productivity
>> _for
>> 
>> Then click on the "Source" link to see the code. (You won't be able to
>> change it without logging in.)
>> 
>> I'm using CASE to pivot tables to separate out monetary versus
>> barter/trade income.
>> 
>> Thanks for any help offered!
>> 
>> ----------------
>> :::: Respond with love and compassion, rather than punishment and
>> retaliation, and an angry person will be disarmed. Trade the need to be
>> right for a loving relationship. It is a trade you - and everyone
>> around you - will benefit from. -- Dean Van Leuven
>> :::: Jan Steinman, EcoReality Co-op ::::
>> 
>> 
>> 
>> 
>> 
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql
> 

----------------
:::: She can't sing in more languages than most people can't speak in. -- JWS
:::: Jan Steinman, EcoReality Co-op ::::




Thread
Making myself crazy...Jan Steinman16 Sep
  • RE: Making myself crazy...Rick James17 Sep
    • Re: Making myself crazy...Jan Steinman19 Sep
      • RE: Making myself crazy...Rick James19 Sep