sql - Counting different ranges over the same dataset efficiently/simply -

-

I am writing a function in Postgresql which will return some metric, will be calculated for a specific time zone (input).

Sample results:

Enter image details here

The main issue is that it is only one metric I need to get another 9 metrics from other tables.

Any suggestions for a less verbose method of achieving this? Return the function dashboard _ metrics (destination_tyimon text) (metric text, count count) today $ func $ DECLARE today TIMESTAMP; Tomorrow TIMESTAMP; Tomorrow TIMESTAMP; The Israeli Timestamp; Last 7 days TIMESTAMP; Last 30days TIMESTAMP; Last 60days TIMESTAMP; Select today's 'Today' life nowadays in destination_timezone; Select (destination_timezone in 'tomorrow' time zone) tomorrow; Select (destination_timezone in 'tomorrow' time zone) tomorrow; SELECT (destination_time in the 'time' time zone) - Interval '1 day' in EARAL; SELECT (destination_tyimension in 'Today' time zone) - '7 days' duration in the last 7 days; Choose (destination_timezone on 'Today' time zone) - Last 30days in '30 days' interval; Select (destination_tyimension in 'Today' time zone) - '60 days interval in the last 60days; Return Query - Todd (SELECT 'ideastoday' :: TEXT AS Metric, COUNT (1) created from WHERE from ANNTX_Idess_arram-> today and created_on & lt; tomorrow and analytics_ideas.space_id = 1) UNION ALL (SELECT 'ideasy yesterday ':: Metric in the form of text, counted from antithesis_ides as COUNT (1) WHERE created on WHERE_On> = tomorrow and created_on & lt; today and analytics_deses.space_id = 1) UNION all (SELECT' ideasereyesterday ':: Texet Metric, COUNT (1) created from WHERE from AS Calculation Analytics_Edes on_on & gt; = Era and Created_O & lt; Tomorrow and Analytics_deses.Space_id = 1) Metrics in the form of UNIX All (SELECT 'ideaslast7days' :: Text) composed of COUNT (1) ANNTX_IDES, where it is built> Last 7 days and created_on & Lieutenant; Today and analytics_ideas.space_id = 1) Metrics in the form of SELECT 'ideaslastlast30days' :: Text, COUNT (1) ANNTX_Edes from WHERE built_on> = last 30days and made_on

At the higher level, this is what usually comes down: If you want to share your metric with logic , then you need to differentiate them Data with . You need a general data structure that separates one metric from the other. With it, you can write a generalized algorithm to process these structures. Dashboard _Matrix (destination_timogen text) Return Tables (metric text, counting count) AS $ func $ with metricidiff (metric, 'initial', -1, 0), ('Ideasieri ',', -2, -1), ('Ideology 7 days', -7, 0)), ('Ideaslast30days', -30, 0), ('ideaslast60days', -60, 0)) Select metricidief Metric, COUNT (*) AM Metricidief, created from Analytics_ides to WHERE_arram = ('Today's Destination_timezone') + Metric Diff.Stattaye * Interval '1 day' and made_on & lt ; (Destination_timogen in the 'time' time zone) + metricidief.exe * interval '1 day' and analytics_idas.space_id = 1 by group metricidief. Metric $ throw $ language static static;

Note that this will not place any order on the output. If this is important, then you should include the DisplayOrder field in MetricDef , and add a ORDER BY anywhere in the main query.

Comments

Post a Comment

Popular posts from this blog

php - PDO bindParam() fatal error -

-
I am trying to learn PDO, now I have written this small code, but it gives me a serious error: Fatal error: Call a member function at a non-object dot () ... $ con = new mysqli ("127.0.0.1", "root", "", "csvdangercheck"); $ Query = $ con- & gt; ("Id,` var1`, `var2`) values ​​(" id,: var1,: var2) ";); $ query- & gt; bypass pattern (': id', $ id); $ query- & gt; BindParam (': var1', $ VAL1); $ Query- & gt; bindParam (': var2', $ val2); $ Query-> Executed (); I Trying to use print_r ($ con-> errorInfo ()) but Fatal error: Undefined method mysqli :: errorInfo ( ) ... can someone tell me what am I missing? Flaffeh said, you have PDO Missing Mysqy, try it: $ con = new PDO ('mysql: host = 127.0.0.1; dbname = csvdangercheck', 'root', '');
Read more

php - How can I cram 6+31 numeric characters into 22 alphanumeric characters? -

-
I have a 6 digit number and a 31-digit number (such as "234536" and "201103231043330478311223582826") that I The API needs to be crawled in the same 22-character alphanumeric field that uses PHP. I tried to convert each one to 32 (using custom code could not handle the base_convert () large number) and to connect with a single-character delimiter, but It only gives me 26 characters This is a restore API, so the letters should be URI-protected. I would like to do a database table cross with two references without reference to another reference value, if possible, any suggestions? Instead use a radix of 62. This will give you 22 characters for the top, 3.35 for the pre and 17.3 for the next. & gt; & Gt; & Gt; Math.log (10 ** 6) /math.log (62) 3.3474826039165504 & gt; & Gt; & Gt; Math.log (10 ** 31) /math.log (62) 17.295326786902177
Read more

logging - How can I log both the Request.InputStream and Response.OutputStream traffic in my ASP.NET MVC3 Application for specific Actions? -

-
For a specific set of tasks, I need to log in to the incoming request inputstream as well as outgoing response. is. I am using an ActionFilterAttribute for this and overriding the OnActionExecuted and OnResultExecuted methods. So I'm starting with this idea ... Public category ActionLoggerAttribute: ActionFilterArrbit {Public Override Zero On-Offed Execution (ActionActioned Contact Filter Filter) {base.OnActionAxecuted (FilterContext); HttpRequestBase request = filterContext.HttpContext.Request; // TODO: Log the request. InputStream} Public Override Zero OnResultExecuted (ResultExecutedContext FilterContext) {base.OnResultExecuted (filterContext); HTTPRPSPointBase Response = FilterContacts Hpptex response; // TODO: Log on Response.OutputStream}} Ideally I'll hook it up with enterprise library logging because I'm already using it for error logging. Am I reaching the input and output stream at the right time? Can I easily use the Enterprise Library to log i...
Read more