Обсуждение: list of all months
Hi,
I want to display data for all days in a month even if no data exists for that month. Some of the days in a month might not have any data at all. With normal query, we can display days only if data exists.But I want to display rows for all days in a month with blank data for non-existing day in database.
How can this be achieved ?
I want to display data for all days in a month even if no data exists for that month. Some of the days in a month might not have any data at all. With normal query, we can display days only if data exists.But I want to display rows for all days in a month with blank data for non-existing day in database.
How can this be achieved ?
<div class="Section1"><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"; color:#1F497D">I recently ran into the same issue and I resolved it by generating a table of nothing but months for the last5 years:</span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"; color:#1F497D">select TO_CHAR((current_date - interval '1 month' * a),'YYYY-MM') AS mmyyyy FROM generate_series(1,60,1)AS s(a)</span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"; color:#1F497D">"2010-02"</span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"; color:#1F497D">"2010-01"</span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"; color:#1F497D">"2009-12"</span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"; color:#1F497D">"2009-11"</span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"; color:#1F497D">"2009-10"</span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"; color:#1F497D">…</span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"; color:#1F497D"> </span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"; color:#1F497D">Then I did a join on this generated series:</span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"; color:#1F497D"> </span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"; color:#1F497D">SELECT months.mmyyyy</span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"; color:#1F497D"> ,COUNT(foo_key) </span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"; color:#1F497D">from (</span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"; color:#1F497D"> select TO_CHAR((current_date - interval '1 month' * a),'YYYY-MM') as mmyyyy </span><p class="MsoNormal"><spanstyle="font-size:11.0pt;font-family:"Calibri","sans-serif"; color:#1F497D"> from generate_series(1,60,1) AS s(a)</span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"; color:#1F497D"> ) months </span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"; color:#1F497D"> LEFT OUTER JOIN foo</span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"; color:#1F497D"> ON months.mmyyyy=to_char(foo_date_created,'YYYY-MM')</span><p class="MsoNormal"><spanstyle="font-size:11.0pt;font-family:"Calibri","sans-serif"; color:#1F497D">GROUP BY months.mmyyyy</span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"; color:#1F497D"> </span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"; color:#1F497D">I’m sure you can adapt this to your needs.</span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"; color:#1F497D"> </span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"; color:#1F497D">Garrett Murphy</span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"; color:#1F497D"> </span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"; color:#1F497D"> </span><div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in"><p class="MsoNormal"><b><spanstyle="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]<b>On Behalf Of </b>query<br /><b>Sent:</b> Monday, March 08, 2010 5:25 AM<br /><b>To:</b>pgsql-sql@postgresql.org<br /><b>Subject:</b> [SQL] list of all months</span></div><p class="MsoNormal"> <p class="MsoNormal">Hi,<br/><br /> I want to display data for all days in a month even if no data exists for that month. Someof the days in a month might not have any data at all. With normal query, we can display days only if data exists.ButI want to display rows for all days in a month with blank data for non-existing day in database.<br /><br /> Howcan this be achieved ?<br /><br /><a href="http://portal.mxlogic.com/redir/?atT74QSkQkjqtSkXI6zB5xAS03F8zG5ezW4DnunM5_FUTW4JPkapo-lyEH0fXkaokWfEitt4-nOAycdCT7PVg_w20ErAwwvV8TvAnXLcIELTKrKrm8Q5zOZ1oYLkN3Uw1lpp76zBc5blFcz7W2N_00jr5NPVJ5dNVNVNAsUrjodCBIo0saCBQQg7OFcQgltd46DDCy1mI9AW6V-7PM76Qjq9JeXb3bUVcQsTdxGcl-Vx8li0S"><span style="text-decoration:none"><imgborder="0" id="_x0000_i1025" src="http://portal.mxlogic.com/images/transparent.gif" /></span></a></div>
-----BEGIN PGP SIGNED MESSAGE----- <br /> Hash: SHA1 <br /> <br /> One approach could be:<br /><br /> You build a tablewith month information over which you are willing to<br /> show data from another table.<br /><br /> Then you just crossjoin your data table and the data in your month table.<br /><br /> Here is some code I use for generating the tablethat holds all the<br /> month I care about.<br /><br /><br /> DROP TABLE IF EXISTS tmp_lookup_months;<br /> CREATETABLE tmp_lookup_months<br /> (month_year_str varchar(7) NOT NULL,<br /> first_day_of_month DATE NOT NULL,<br/> month INTEGER NOT NULL,<br /> year INTEGER NOT NULL,<br /> PRIMARY KEY (first_day_of_month, year),<br/> <br /> CONSTRAINT valid_date<br /> CHECK (<br /> (EXTRACT (YEAR FROM first_day_of_month)::integer= year) AND<br /> (EXTRACT (MONTH FROM first_day_of_month)::integer = month) AND<br/> (EXTRACT (MONTH FROM first_day_of_month) > 0) AND<br /> (EXTRACT (MONTH FROM first_day_of_month)< 13) AND <br /> (EXTRACT (DAY FROM first_day_of_month) = 01) AND<br /> (month_year_str) like (CASE WHEN month <= 9<br /> then<br /> cast (year::text ||'-0'||month::text as char(7)) <br /> <br /> else<br /> cast (year::text||'-'|| month::text as char(7)) <br /> end)<br /> ) <br /> );<br /><br /><br /> INSERT INTO tmp_lookup_months<br /> select month_year_str, first_day_of_month, month, year from <br /> (<br /> selectmonth, year,<br /> CASE WHEN month <= 9<br /> then<br /> cast (year::text ||'-0'||month::text as char(7)) <br /> <br /> else<br /> cast (year::text||'-'|| month::text as char(7)) <br /> <br /> end as month_year_str, <br /> cast (year::text||'-'||month||'-1'as date) as<br /> first_day_of_month <br /> from<br /> generate_series(1990,2090) as year cross join<br /> generate_series(1,12) as month<br /> order by year, month<br/> ) as t1;<br /><br /><br /> What is nice about this approach is that you can easily change the<br /> granularityof the time over which you are willing to show the info so<br /> you can create a second table with a trimesterlist for example.<br /><br /><br /> Then say you have your data in a table called mydata.<br /><br /><br /> select <br /><br /> ...<br /> from mydata<br /><br /> CROSS JOIN tmp_lookup_months as ym where(<br /> ym.year >=$1 and ym.year <= $2<br /> and my_intersection_function(start_date, end_date, ym.month, ym.year)>0<br /> )<br/> order by ...<br /><br /> So I'm assuming here that in the mydata table you have at least 4 columns:<br /> id, start_date,end_date, some_data.<br /> some_data field probably only makes sense over the start_date to<br /> end_date interval.<br/> So in your select query you'll most likely need an aggregate function.<br /><br /><br /> If you could provideda more complete description of what you are<br /> trying to achive I might be able to further help.<br /><br /> PetruGhita<br /><br /> On 08/03/2010 13:25, query wrote:<br /><span style="white-space: pre;">> Hi,<br /> > <br />> I want to display data for all days in a month even if no data <br /> > exists for that month. Some of the daysin a month might not have <br /> > any data at all. With normal query, we can display days only if<br /> > dataexists.But I want to display rows for all days in a month with<br /> > blank data for non-existing day in database.<br/> > <br /> > How can this be achieved ?<br /> > <br /> > <a class="moz-txt-link-rfc2396E" href="http://sigads.rediff.com/RealMedia/ads/click_nx.ads/www.rediffmail.com/signatureline.htm@Middle?"><http://sigads.rediff.com/RealMedia/ads/click_nx.ads/www.rediffmail.com/signatureline.htm@Middle?></a></span><br />-----BEGIN PGP SIGNATURE----- <br /> Version: GnuPG v1.4.9 (MingW32) <br /> Comment: Using GnuPG with Mozilla - <a class="moz-txt-link-freetext"href="http://enigmail.mozdev.org/">http://enigmail.mozdev.org/</a><br /> <br /> iEYEARECAAYFAkueqZsACgkQt6IL6XzynQT+rgCguhFx6qzH3sgiti3O5zaqVQYS<br /> ra4Anjz1C8hS5YC6jRVD9coV6j1AxpPv <br /> =OoAd <br/> -----END PGP SIGNATURE----- <br /><br />
On Mon, Mar 8, 2010 at 13:25, query <search2006@rediffmail.com> wrote: > > Hi, > > I want to display data for all days in a month even if no data exists for that month. Some of the days in a month mightnot have any data at all. With normal query, we can display days only if data exists.But I want to display rows forall days in a month with blank data for non-existing day in database. > > How can this be achieved ? Say, you have a table like: CREATE TABLE some_data ( date date NOT NULL, some_value int ); Now, You would like to print values from March 2010, even if there is no entry for some days in such a table. We need to have a list of all the days in March. We can do it with a query: SELECT date '2010-03-01' + n AS date FROM generate_series(0, date '2010-04-01' - date'2010-03-01' - 1) AS x(n); Sweet, we have dates, we just need a LEFT JOIN now: SELECT date, coalesce(value, 0) AS value FROM some_data RIGHT JOIN ( SELECT date '2010-03-01' + n AS date FROM generate_series(0, date '2010-04-01' - date'2010-03-01' - 1) AS x(n) ) AS dates USING (date); If you are running fairy recent PostgreSQL it could be written even nicer: WITH dates AS ( SELECT date '2010-03-01' + n AS date FROM generate_series(0, date '2010-04-01' - date'2010-03-01' -1) AS x(n) ) SELECT date, coalesce(value, 0) AS value FROM dates LEFT JOIN some_data USING (date); Two remarks:- it is fairy easy to create generate_series(date, date) function. Give it a try - its fun! :)- coalesce functionwill provide 0 in places where there is no data row, or value is NULL. Best regards, Dawid
It looks like a procedural problem. I would solve it in plpgsql.
On 2010-03-08, query <search2006@rediffmail.com> wrote: > --=_484d28810a276e7b5e461f0328ee205f > Content-Transfer-Encoding: 7bit > Content-Type: text/plain; charset="UTF-8" > > Hi, > > I want to display data for all days in a month even if no data > exists for that month. Some of the days in a month might not have any > data at all. With normal query, we can display days only if data > exists.But I want to display rows for all days in a month with blank > data for non-existing day in database. > > How can this be achieved ? > an outer join to (select FIRST_DAY_OF_MONTH + generate_series(1,DAYS_IN_MONTH) -1 ) where the date columns match FIRST_DAY_OF_MONTH and DAYS_IN_MONTH are to be replaced (by you) with the apropriate expressions by some means, possibly date arithmetic.