[Swansea Hackspace] SQL Help
Emyr Morris
em at preseli.com
Wed May 20 20:28:08 BST 2015
it is amazing how a bit of tidying up makes such a difference! Chuffed for
you.
Smashes that old saying "A problem shared is a problem halved"!
Emyr
On 20 May 2015 at 17:03, Graham Owens <grahamowensuk at googlemail.com> wrote:
> Better??
>
> Column Type Null Default Comments xxxx_id int(11) No
> xxxx_modified timestamp No CURRENT_TIMESTAMP xxxx_created timestamp
> No 0000-00-00 00:00:00 xxxx_title varchar(16) No xxxx_first
> varchar(50) No xxxx_last varchar(50) No xxxx_dob date No
> xxxx_active int(4) No 0 xxxx_start_date date No
> xxxx_exp_end_date date No xxxx_start_payment_value decimal(10,2) No
> xxxx_start_payment_claim_date date Yes *NULL*
> xxxx_outcome_payment_value decimal(10,2) No
> xxxx_outcome_payment_claim_date date Yes *NULL*
>
> Column Type Null Default Comments ms_id int(11) No xxxx_id
> int(11) No ms_weight tinyint(4) No ms_exp_date date No
> ms_act_date date Yes *NULL* ms_claim_date date Yes *NULL*
> ms_value decimal(6,2) No
>
>
> SELECT EXTRACT(YEAR_MONTH FROM `calendar_month`.`calendar_month_date`) AS
> period, COUNT(*) as ms_count, IFNULL(SUM(`milestone_value`),0) as ms_value,
> (
> SELECT COUNT(*)
> FROM `xxxx`
> WHERE EXTRACT(YEAR_MONTH FROM `xxxx_exp_end_date`)=EXTRACT(YEAR_MONTH
> FROM `calendar_month`.`calendar_month_date`) AND
> `xxxx_outcome_payment_claim_date` IS NULL
> ) as fin_count,
> IFNULL((
> SELECT SUM(`xxxx_outcome_payment_value`)
> FROM `xxxx`
> WHERE EXTRACT(YEAR_MONTH FROM `xxxx_exp_end_date`)=EXTRACT(YEAR_MONTH
> FROM `calendar_month`.`calendar_month_date`) AND
> `xxxx_outcome_payment_claim_date` IS NULL
> ),0) as fin_value
> FROM `ms`
> RIGHT JOIN `calendar_month`
> ON EXTRACT(YEAR_MONTH FROM `ms`.`ms_exp_date`)=EXTRACT(YEAR_MONTH FROM
> `calendar_month`.`calendar_month_date`)
> WHERE `calendar_month`.`calendar_month_date` BETWEEN DATE_FORMAT(NOW()
> ,'%Y-%m-01') AND DATE_ADD(DATE_FORMAT(NOW() ,'%Y-%m-01'),INTERVAL 12 MONTH)
> AND `ms`.`ms_claim_date` IS NULL
> GROUP BY EXTRACT(YEAR_MONTH FROM `calendar_month`.`calendar_month_date`)
> LIMIT 12
>
> Managed to get it down from *35.6576* seconds to *0.0139* seconds [?]
>
> Thanks everyone. Can this be made any more efficient, table row counts
> for 'ms' just now are 10's of thousands, but will be into the several
> hundred thousand (possibly millions within a year)
>
>
>
> On 18 May 2015 at 16:00, Emyr Morris <em at preseli.com> wrote:
>
>> I can show you some real world examples later of working tables full of
>> data. I find it much easier to explain that way
>>
>>
>> On Monday, May 18, 2015, Graham Owens <grahamowensuk at googlemail.com>
>> wrote:
>>
>>> Justin, I think you might be right - we can thrash it out a bit more
>>> later
>>>
>>> Thanks for all the input, once we have a chat later - and formalize the
>>> problem better (there are normalization rules????? - just kidding - i was
>>> between a rock and a hard place, this works - just needs improved)
>>>
>>> If it still is giving me problems, we can pick up the conversation then.
>>>
>>> Thanks
>>>
>>> G
>>>
>>> On 18 May 2015 at 15:06, Justin Mitchell <justin at discordia.org.uk>
>>> wrote:
>>>
>>>> On Mon, 2015-05-18 at 14:24 +0100, Graham Owens wrote:
>>>>
>>>>
>>>> > table1
>>>> > table1.id: autonum
>>>> > active: bool
>>>> > t2id_1: int
>>>> > t2id_2: int
>>>> > t2id_3: int
>>>> > t2id_4: int
>>>> > t2id_5: int
>>>> > t2id_6: int
>>>> >
>>>> >
>>>> >
>>>> > table2
>>>> > table2.id: autonum
>>>> > expected_date: datetime
>>>> > actual_date: datetime
>>>> > claim_date: datetime
>>>> > value: datetime
>>>>
>>>> There are several questions about the data structure that need to be
>>>> asked here, will one table2 entry only be used once, or could it be
>>>> referenced by multiple table1 entries ?
>>>>
>>>> if the former, then the t2id_* fields are unnessary
>>>> if the latter, then they should be replaced with a link table.
>>>>
>>>> but the list might not be the place to thrash this out, will see you
>>>> later.
>>>>
>>>>
>>>>
>>>> _______________________________________________
>>>> Hackspace mailing list
>>>> Hackspace at swansea.hackspace.org.uk
>>>> http://swansea.hackspace.org.uk/mailman/listinfo/hackspace
>>>>
>>>
>>>
>>
>> --
>> This e-mail and any files transmitted with it are confidential and
>> intended solely for the use of the individual or entity to whom they are
>> addressed.
>>
>> Mae'r e-bost hwn ac unrhyw ffeiliau a drosglwyddir gydag ef yn
>> gyfrinachol ac at ddefnydd yr unigolyn neu'r corff y cyfeiriwyd hwy atynt
>> yn unig.
>>
>>
>> _______________________________________________
>> Hackspace mailing list
>> Hackspace at swansea.hackspace.org.uk
>> http://swansea.hackspace.org.uk/mailman/listinfo/hackspace
>>
>>
> On 18 May 2015 at 16:00, Emyr Morris <em at preseli.com> wrote:
>
>> I can show you some real world examples later of working tables full of
>> data. I find it much easier to explain that way
>>
>>
>> On Monday, May 18, 2015, Graham Owens <grahamowensuk at googlemail.com>
>> wrote:
>>
>>> Justin, I think you might be right - we can thrash it out a bit more
>>> later
>>>
>>> Thanks for all the input, once we have a chat later - and formalize the
>>> problem better (there are normalization rules????? - just kidding - i was
>>> between a rock and a hard place, this works - just needs improved)
>>>
>>> If it still is giving me problems, we can pick up the conversation then.
>>>
>>> Thanks
>>>
>>> G
>>>
>>> On 18 May 2015 at 15:06, Justin Mitchell <justin at discordia.org.uk>
>>> wrote:
>>>
>>>> On Mon, 2015-05-18 at 14:24 +0100, Graham Owens wrote:
>>>>
>>>>
>>>> > table1
>>>> > table1.id: autonum
>>>> > active: bool
>>>> > t2id_1: int
>>>> > t2id_2: int
>>>> > t2id_3: int
>>>> > t2id_4: int
>>>> > t2id_5: int
>>>> > t2id_6: int
>>>> >
>>>> >
>>>> >
>>>> > table2
>>>> > table2.id: autonum
>>>> > expected_date: datetime
>>>> > actual_date: datetime
>>>> > claim_date: datetime
>>>> > value: datetime
>>>>
>>>> There are several questions about the data structure that need to be
>>>> asked here, will one table2 entry only be used once, or could it be
>>>> referenced by multiple table1 entries ?
>>>>
>>>> if the former, then the t2id_* fields are unnessary
>>>> if the latter, then they should be replaced with a link table.
>>>>
>>>> but the list might not be the place to thrash this out, will see you
>>>> later.
>>>>
>>>>
>>>>
>>>> _______________________________________________
>>>> Hackspace mailing list
>>>> Hackspace at swansea.hackspace.org.uk
>>>> http://swansea.hackspace.org.uk/mailman/listinfo/hackspace
>>>>
>>>
>>>
>>
>> --
>> This e-mail and any files transmitted with it are confidential and
>> intended solely for the use of the individual or entity to whom they are
>> addressed.
>>
>> Mae'r e-bost hwn ac unrhyw ffeiliau a drosglwyddir gydag ef yn
>> gyfrinachol ac at ddefnydd yr unigolyn neu'r corff y cyfeiriwyd hwy atynt
>> yn unig.
>>
>>
>> _______________________________________________
>> Hackspace mailing list
>> Hackspace at swansea.hackspace.org.uk
>> http://swansea.hackspace.org.uk/mailman/listinfo/hackspace
>>
>>
>
> _______________________________________________
> Hackspace mailing list
> Hackspace at swansea.hackspace.org.uk
> http://swansea.hackspace.org.uk/mailman/listinfo/hackspace
>
>
--
This e-mail and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed.
Mae'r e-bost hwn ac unrhyw ffeiliau a drosglwyddir gydag ef yn gyfrinachol
ac at ddefnydd yr unigolyn neu'r corff y cyfeiriwyd hwy atynt yn unig.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://swansea.hackspace.org.uk/pipermail/hackspace/attachments/20150520/504677db/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: 360.gif
Type: image/gif
Size: 453 bytes
Desc: not available
URL: <http://swansea.hackspace.org.uk/pipermail/hackspace/attachments/20150520/504677db/attachment.gif>
More information about the Hackspace
mailing list