Let’s say you have a process that requires a start date, but may or may not have an end date, as a parameter feeding in. How can you accomplish this in Netezza via a stored procedure? The internet has not had great answers to this question but it’s not as hard as some other programmers make it out to be. So today we tackle optional arguments – in easy mode. Continue reading
code-snippets
Calculating Percentiles in PostgreSQL
Standard
Slicing data into manageable chunks for viewing is crucial when you start dealing with more records than will fit in something like Excel (without PowerPivot, of course). One of the most common ways to look at data in a more easily-digestible manner is to use percentiles, or some derivative thereof, to group records based on a ranking. This allows you to then compare equal-sized groups to one another in order to form conclusions as to relative behavior.
Most SQL variants have workarounds for how to accomplish this task that may or may not actually cover 100% of your data (may drop a few records here or there when trying to round a percentage to a whole number of records to pull). PostgreSQL, on the other hand, has a handy function built in for doing this sort of thing without having to worry about getting full coverage on your table. Continue reading
Regular Expression in PostgreSQL
Standard
There are a whole set of fields in the databases I’m using here that are tilde-delimited (~) varchar strings with a mess of key-value pairs, the values from which I really need. Unfortunately, since they are varying character lengths, in no particular set order within that field, it is impossible to substring your way efficiently through them. Thankfully, there is a RegEx genius on my team who produced a handy chuck of code that pgSQL can easily recognize, parse and process for pulling precisely what I need. Continue reading
Zero Padding in PostgreSQL
Standard
Fun new conundrum in dealing with data formats/displays in PostgreSQL! A client recently requested that we provide a data extract that included percentages as a three character number, left zero-padded, rather than a decimal/numeric. There’s not a great way in Postgres to show a numeric with leading zeros (actually, I’ve yet to find a data type that does this consistently as a built-in to any platform). Instead, you have to do a little bit of work to get to your end result as a character (or text) field. Continue reading
Split Field by Line Break in PostgreSQL
Standard
In one of the PostgreSQL systems we work with often, there is only one street address field. To get around the need for a change to the table structure, our IT folks simply use a new line character (\n) to denote that there is a second line of address information. The problem becomes, when we go to generate a mailing list, how can we export a list to send to the mailhouse with all address information on one line?
The answer is to use the SPLIT_PART function. See the example below: Continue reading
Changing Sort Order in StudioPress Themes
Standard
Hit a rather irksome problem recently in developing a WordPress website on the StudioPress Genesis framework. I could not find anywhere to change the default sort order (Newest to Oldest by time of publication). Nothing in the functions. Nothing I could find in the lib settings. Nothing in the theme options (which I believe should be changed).
Finally, after hearing back from the developers and having them point me to a nice, clean function for supposedly resolving this, I gave it a try. It had the unfortunate side effect of knocking out my static homepage because it went into the loop, thought the homepage had a loop, and decided to simply sort all the posts in the database into that page. At least it showed them in the specified order.
Thankfully, I came up with a quick way to resolve this pesky problem. All you have to do is wrap the function with a check to determine if it is a page. Here’s the code: Continue reading
Randomizing Random Selection
Standard
This one had been bugging me for a while now. There are a lot of analyses where it is useful to select multiple random groups. Usually, this would involve picking a bunch of numbers out of your head and trying them as the seed values (I like using phone numbers without the area codes – then I can call the person and tell them they rocked my randomization).
But today, as I struggled with pulling a multitude of sample sets, I decided to come up with a more elegant solution for generating random number seed values. Behold the random loop: Continue reading
Calculating Months Between Two Dates in PostgreSQL
Standard
One of the most basic calculations on dates is to tell the time elapsed between two dates. Often it is more helpful to show the date as a number of months rather than a number of days. In PostgreSQL, subtracting one date from another will yield a number of days that then takes a tremendously complex formula to manipulate into months.
The best way I have found to get around this is to use the built in AGE function. The age function calculates the difference between two dates and returns an interval. This may not seem like we’ve gotten very far in calculating the number of months between the two dates, but stick with me on this one. You can then EXTRACT the pieces of the interval needed to calculate months and use them in a simple equation.
EXTRACT(year FROM age(end_date,start_date))*12 + EXTRACT(month FROM age(end_date,start_date))