select from multiple tables and sum vs join and sum

I am doing internship in an advertising company, i already implemented a tool to gather all the necessary data form facebook & import them on a database.

Now i am trying to manipulate that data, first by making some test cases & getting some results. The tables grow by 35k rows per day so after a month of using the tool i noticed that the query i use to obtain the sum of certain adcreatives clicks is starting to slow down.

i am asking if the query i use can speed up if i use it with a join & how.

here is the query i have for the sum of clicks per adcreative (with adgroup_id,campaign_id as connect to the other tables):

<!-- language-all: lang-sql -->SELECT t1.adgroup_id, t1.campaign_id, t1.creative_ids, SUM( t2.clicks ) AS clicksFROM adgroups t1, adgroup_stats t2WHERE t1.adgroup_id = t2.adgroup_idGROUP BY t1.creative_idsORDER BY clicks DESC 

currently the query takes 3 secs to complete on a dedicated server, i guess after 6 months it will be at more than 60 secs or so as the tables grow.

edit: here is the explain of the query ( although this is the first time i actually use it & not so sure what it means)

id  select_type table   type    possible_keys   key key_len ref rows    Extra1   SIMPLE  t2  ALL PRIMARY NULL    NULL    NULL    671549  Using temporary; Using filesort1   SIMPLE  t1  ref PRIMARY PRIMARY 8   fbads.t2.adgroup_id 358 Using index

That looks like a full table scan, & with that rapid growth small performance changes won’t make a huge difference on the long run. You need a different approach.

I would calculate aggregates for the previous months (days, etc) with a cron job, & when you need stats then merge that with the fresh results (using the query you already wrote). That why you only have to scan the fresh record, which means the queries is going to be fast.

Alternatively, you can keep up-to-date counters in the adgroups table, & update them on each click. Not sure if mysql is the right tool for this, I can recommend MongoDB, it can do very swift atomic increments on fields, & though it doesn’t donate you as strict guarantees (ACID) as a relational database, in this case it’s not a problem, ad clicks aren’t mission critical data, nobody is going to complain, if you lose < 0.01% percent of click information.

SQL and number combination search

I have table with 10 number fields (let’s say F1, F2... F10).

Now I have 4 numbers (N1, N2, N3, N4).

I have to find if those 4 numbers appear anywhere in the above table. For example, if F2=N4 & F1=N2 & Fx=N3 & Fy=N1 (any order, any combination).

I was wondering is there quick way to do it via SQL or is it only way to write looooong combination of selects (I am not sure I will be able even complete that in this life time).

Here is SQLFiddel Demo

Below is the sample Query

select * from Tempwhere 'N1' in (F1,F2,F3,F4,F5,F6,F7,F8,F9,F10)  & 'N2' in (F1,F2,F3,F4,F5,F6,F7,F8,F9,F10)  & 'N3' in (F1,F2,F3,F4,F5,F6,F7,F8,F9,F10)  & 'N4' in (F1,F2,F3,F4,F5,F6,F7,F8,F9,F10)

how to add multiple columns to a group by case?

I have the following group by clause on my query which uses a case statement yet I want to add another column to the group by too:

        group by case @dateTypeWHEN 'Daily' then i.overridedateWHEN 'Weekly' then dateadd(day,-1*datepart(weekday,i.overridedate)+1,i.overridedate) WHEN 'Monthly' THEN DATEADD(day, -1*DATEpart(day,i.overridedate)+1, i.overridedate) WHEN 'Quarterly' THEN dateadd(mm,-3,DATEADD(qq, DATEDIFF(qq,0,i.overridedate )+1, 0)) END

how do I add another column to this group by?

group by case @dateTypeWHEN 'Daily' then i.overridedateWHEN 'Weekly' then dateadd(day,-1*datepart(weekday,i.overridedate)+1,i.overridedate) WHEN 'Monthly' THEN DATEADD(day, -1*DATEpart(day,i.overridedate)+1, i.overridedate) WHEN 'Quarterly' THEN dateadd(mm,-3,DATEADD(qq, DATEDIFF(qq,0,i.overridedate )+1, 0)) END--Edited,col1,col2,etc..,coln

self join on one table

I have a table STARTSTOP

ACTION  DATA                    ID_PPSTARTSTOPPOZ0   2013-03-18 08:38:00 104511   2013-03-18 09:00:00 104530   2013-03-18 09:50:00 104661   2013-03-18 10:38:00 104670   2013-03-19 11:54:00 104991   2013-03-19 12:32:00 10505

Action 0 -> START ACTION
Action 1 -> STOP ACTION
DATA is a timestamp of action

I would like to run a select statement that would return records something like:

ACTION_1   ACTION_2    DURATION10451        10453       2210466        10466       48             ...

OR summary for all actions duration in one row.

Is it feasible with a single database query? (without creating additional tables)

select A1.ID_PPSTARTSTOPPOZ as Action_0,       A2.Action_1,       datediff (minute, A1.DATA ,A2.DATA)from STARTSTOP A1JOIN (  select ID_PPSTARTSTOPPOZ as Action_1,         DATA,         (select max(ID_PPSTARTSTOPPOZ)           FROM STARTSTOP           where ID_PPSTARTSTOPPOZ<T.ID_PPSTARTSTOPPOZ                AND                ACTION=0) AS PREV_ACTION  from STARTSTOP T  where ACTION=1 ) A2 on A1.ID_PPSTARTSTOPPOZ=A2.PREV_ACTIONwhere ACTION = 0order by A1.ID_PPSTARTSTOPPOZ 

DATEDIFF function

SQLFiddle Example for MSSQL yet it has to work under Firebird too

SQL Server date formatting from string

We’ve recently migrated our database to a different server & since this I think the date format querying has changed somehow.

Previously we could use the following..

SELECT * FROM table WHERE date > 'YYYY-MM-DD'

However now we have to use..

SELECT * FROM table WHERE date > 'YYYY-DD-MM'

Can someone tell me what I need to alter to obtain back to the previous version?

Try this one –

Query:

SET DATEFORMAT ymd

Read current settings:

DBCC USEROPTIONS

Output:

Set Option                 Value-------------------------- -----------------...language                   us_englishdateformat                 ymd...

Use Aggregate Function in UNION ALL result set

How can I use aggregate Functions in UNION ALL Resultset

FOR EXAMPLE

SELECT A,B FROM MyTableUNION ALLSELECT B,C FROM MYAnotherTable

Result Set Would Be

    A  B--------------    1  2    3  4    4  5    6  7

When I tried to obtain MAX(A) it returns 3. I want 6.

When I tried to obtain MAX(B) it returns 4. I want 7.

Other than Max(), Can I obtain another aggregate function which user defined?

For example:

(SELECT TOP 1 A WHERE B=5)

Try this way:

select max(A)from(      SELECT A,B FROM MyTable      UNION ALL      SELECT B,C FROM MYAnotherTable    ) Tab

If the column A is varchar (You said that in the comment below) try this way:

select max(A)from(      SELECT cast(A as int) as A,B FROM MyTable      UNION ALL      SELECT B,C FROM MYAnotherTable    ) Tab

With TOP 1

select max(A)from(      SELECT top 1 cast(A as int) as A,B FROM MyTable      UNION ALL      SELECT B,C FROM MYAnotherTable    ) Tab