[Swansea Hackspace] SQL Help
Graham Owens
grahamowensuk at googlemail.com
Wed May 20 17:03:53 BST 2015
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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://swansea.hackspace.org.uk/pipermail/hackspace/attachments/20150520/fede7fbd/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/fede7fbd/attachment.gif>
More information about the Hackspace
mailing list