PostgreSQL Advanced Features : Window Functions

A window function performs a calculation across a set of table rows that are somehow related to the
current row. This is comparable to the type of calculation that can be done with an aggregate function.
But unlike regular aggregate functions, use of a window function does not cause rows to become
grouped into a single output row — the rows retain their separate identities. Behind the scenes, the
window function is able to access more than just the current row of the query result.
Here is an example that shows how to compare each employee’s salary with the average salary in his
or her department:

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;

depname | empno | salary | avg
-----------+-------+--------+-----------------------
develop | 11 | 5200 | 5020.0000000000000000
develop | 7 | 4200 | 5020.0000000000000000
develop | 9 | 4500 | 5020.0000000000000000
develop | 8 | 6000 | 5020.0000000000000000
develop | 10 | 5200 | 5020.0000000000000000
personnel | 5 | 3500 | 3700.0000000000000000
personnel | 2 | 3900 | 3700.0000000000000000
sales | 3 | 4800 | 4866.6666666666666667
sales | 1 | 5000 | 4866.6666666666666667
sales | 4 | 4800 | 4866.6666666666666667
(10 rows)

The first three output columns come directly from the table empsalary, and there is one output row
for each row in the table. The fourth column represents an average taken across all the table rows that
have the same depname value as the current row. (This actually is the same function as the regular avg
aggregate function, but the OVER clause causes it to be treated as a window function and computed
across an appropriate set of rows.)
A window function call always contains an OVER clause directly following the window function’s
name and argument(s). This is what syntactically distinguishes it from a regular function or aggregate
function. The OVER clause determines exactly how the rows of the query are split up for processing by
the window function. The PARTITION BY list within OVER specifies dividing the rows into groups, or
partitions, that share the same values of the PARTITION BY expression(s). For each row, the window
function is computed across the rows that fall into the same partition as the current row.
You can also control the order in which rows are processed by window functions using ORDER BY
within OVER. (The window ORDER BY does not even have to match the order in which the rows are
output.) Here is an example:

SELECT depname, empno, salary,
rank() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;

depname | empno | salary | rank
-----------+-------+--------+------
develop | 8 | 6000 | 1
develop | 10 | 5200 | 2
develop | 11 | 5200 | 2
develop | 9 | 4500 | 4
develop | 7 | 4200 | 5
personnel | 2 | 3900 | 1
personnel | 5 | 3500 | 2
sales | 1 | 5000 | 1
sales | 4 | 4800 | 2
sales | 3 | 4800 | 2
(10 rows)

As shown here, the rank function produces a numerical rank within the current row’s partition for
each distinct ORDER BY value, in the order defined by the ORDER BY clause. rank needs no explicit
parameter, because its behavior is entirely determined by the OVER clause.
The rows considered by a window function are those of the “virtual table” produced by the query’s
FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row
removed because it does not meet the WHERE condition is not seen by any window function. A query
can contain multiple window functions that slice up the data in different ways by means of different
OVER clauses, but they all act on the same collection of rows defined by this virtual table.
We already saw that ORDER BY can be omitted if the ordering of rows is not important. It is also
possible to omit PARTITION BY, in which case there is just one partition containing all the rows.
There is another important concept associated with window functions: for each row, there is a set of
rows within its partition called its window frame. Many (but not all) window functions act only on
the rows of the window frame, rather than of the whole partition. By default, if ORDER BY is supplied
then the frame consists of all rows from the start of the partition up through the current row, plus any
following rows that are equal to the current row according to the ORDER BY clause. When ORDER BY
is omitted the default frame consists of all rows in the partition.
Here is an example using sum:

SELECT salary, sum(salary) OVER () FROM empsalary;

salary | sum
--------+-------
5200 | 47100
5000 | 47100
3500 | 47100
4800 | 47100
3900 | 47100
4200 | 47100
4500 | 47100
4800 | 47100
6000 | 47100
5200 | 47100
(10 rows)

Above, since there is no ORDER BY in the OVER clause, the window frame is the same as the partition,
which for lack of PARTITION BY is the whole table; in other words each sum is taken over the whole
table and so we get the same result for each output row. But if we add an ORDER BY clause, we get
very different results:

SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;

salary | sum
--------+-------
3500 | 3500
3900 | 7400
4200 | 11600
4500 | 16100
4800 | 25700
4800 | 25700
5000 | 30700
5200 | 41100
5200 | 41100
6000 | 47100
(10 rows)

Here the sum is taken from the first (lowest) salary up through the current one, including any duplicates
of the current one (notice the results for the duplicated salaries).
Window functions are permitted only in the SELECT list and the ORDER BY clause of the query. They
are forbidden elsewhere, such as in GROUP BY, HAVING and WHERE clauses. This is because they
logically execute after the processing of those clauses. Also, window functions execute after regular
aggregate functions. This means it is valid to include an aggregate function call in the arguments of a
window function, but not vice versa.
If there is a need to filter or group rows after the window calculations are performed, you can use a
sub-select. For example:

SELECT depname, empno, salary, enroll_date
FROM
(SELECT depname, empno, salary, enroll_date,
rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
FROM empsalary
) AS ss
WHERE pos < 3;

The above query only shows the rows from the inner query having rank less than 3.
When a query involves multiple window functions, it is possible to write out each one with a separate
OVER clause, but this is duplicative and error-prone if the same windowing behavior is wanted for
several functions. Instead, each windowing behavior can be named in a WINDOW clause and then
referenced in OVER. For example:

SELECT sum(salary) OVER w, avg(salary) OVER w
FROM empsalary
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);

PostgreSQL Advanced Features : Transactions

We will now discuss some more advanced features of SQL that simplify management and prevent loss or corruption of your data.
Transactions are a fundamental concept of all database systems. The essential point of a transaction is
that it bundles multiple steps into a single, all-or-nothing operation. The intermediate states between
the steps are not visible to other concurrent transactions, and if some failure occurs that prevents the
transaction from completing, then none of the steps affect the database at all.
For example, consider a bank database that contains balances for various customer accounts, as well
as total deposit balances for branches. Suppose that we want to record a payment of $100.00 from
Arthur’s account to Bob’s account. Simplifying outrageously, the SQL commands for this might look
like:

UPDATE accounts SET balance = balance - 100.00
WHERE name = ’Arthur’;
UPDATE branches SET balance = balance - 100.00
WHERE name = (SELECT branch_name FROM accounts WHERE name = ’Arthur’);
UPDATE accounts SET balance = balance + 100.00
WHERE name = ’Bob’;
UPDATE branches SET balance = balance + 100.00
WHERE name = (SELECT branch_name FROM accounts WHERE name = ’Bob’);

The details of these commands are not important here; the important point is that there are several
separate updates involved to accomplish this rather simple operation. Our bank’s officers will want to
be assured that either all these updates happen, or none of them happen. It would certainly not do for
a system failure to result in Bob receiving $100.00 that was not debited from Arthur. Nor would Arthur
long remain a happy customer if she was debited without Bob being credited. We need a guarantee
that if something goes wrong partway through the operation, none of the steps executed so far will
take effect. Grouping the updates into a transaction gives us this guarantee. A transaction is said to
be atomic: from the point of view of other transactions, it either happens completely or not at all.
We also want a guarantee that once a transaction is completed and acknowledged by the database
system, it has indeed been permanently recorded and won’t be lost even if a crash ensues shortly
thereafter. For example, if we are recording a cash withdrawal by Bob, we do not want any chance that
the debit to his account will disappear in a crash just after he walks out the bank door. A transactional
database guarantees that all the updates made by a transaction are logged in permanent storage (i.e.,
on disk) before the transaction is reported complete.
Another important property of transactional databases is closely related to the notion of atomic updates:
when multiple transactions are running concurrently, each one should not be able to see the
incomplete changes made by others. For example, if one transaction is busy totalling all the branch
balances, it would not do for it to include the debit from Arthur’s branch but not the credit to Bob’s
branch, nor vice versa. So transactions must be all-or-nothing not only in terms of their permanent
effect on the database, but also in terms of their visibility as they happen. The updates made so far by
an open transaction are invisible to other transactions until the transaction completes, whereupon all
the updates become visible simultaneously.
In PostgreSQL, a transaction is set up by surrounding the SQL commands of the transaction with
BEGIN and COMMIT commands. So our banking transaction would actually look like:

BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = ’Arthur’;
-- etc etc
COMMIT;

If, partway through the transaction, we decide we do not want to commit (perhaps we just noticed that
Arthur’s balance went negative), we can issue the command ROLLBACK instead of COMMIT, and all our
updates so far will be canceled.
PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not
issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful)
COMMIT wrapped around it. A group of statements surrounded by BEGIN and COMMIT is sometimes
called a transaction block.
Note: Some client libraries issue BEGIN and COMMIT commands automatically, so that you might
get the effect of transaction blocks without asking. Check the documentation for the interface you
are using.
It’s possible to control the statements in a transaction in a more granular fashion through the use of
savepoints. Savepoints allow you to selectively discard parts of the transaction, while committing the
rest. After defining a savepoint with SAVEPOINT, you can if needed roll back to the savepoint with
ROLLBACK TO. All the transaction’s database changes between defining the savepoint and rolling
back to it are discarded, but changes earlier than the savepoint are kept.
After rolling back to a savepoint, it continues to be defined, so you can roll back to it several times.
Conversely, if you are sure you won’t need to roll back to a particular savepoint again, it can be
released, so the system can free some resources. Keep in mind that either releasing or rolling back to
a savepoint will automatically release all savepoints that were defined after it.
All this is happening within the transaction block, so none of it is visible to other database sessions.
When and if you commit the transaction block, the committed actions become visible as a unit to
other sessions, while the rolled-back actions never become visible at all.
Remembering the bank database, suppose we debit $100.00 from Arthur’s account, and credit Bob’s
account, only to find later that we should have credited Wally’s account. We could do it using savepoints
like this:

BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = ’Arthur’;
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = ’Bob’;
-- oops ... forget that and use Wally’s account
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = ’Wally’;
COMMIT;

This example is, of course, oversimplified, but there’s a lot of control possible in a transaction block
through the use of savepoints. Moreover, ROLLBACK TO is the only way to regain control of a transaction
block that was put in aborted state by the system due to an error, short of rolling it back completely
and starting again.

PostgreSQL Advanced Features : Views and Foreign Keys

We will now discuss some more advanced features of SQL that simplify management and prevent loss or corruption of your data. Finally, we will look at some PostgreSQL extensions.

Views

Suppose the combined listing of weather records and city
location is of particular interest to your application, but you do not want to type the query each time
you need it. You can create a view over the query, which gives a name to the query that you can refer
to like an ordinary table:

CREATE VIEW myview AS
SELECT city, temp_lo, temp_hi, prcp, date, location

SELECT * FROM weather, cities
WHERE city = name;
FROM myview;

Making liberal use of views is a key aspect of good SQL database design. Views allow you to encapsulate
the details of the structure of your tables, which might change as your application evolves,
behind consistent interfaces.
Views can be used in almost any place a real table can be used. Building views upon other views is
not uncommon.

Foreign Keys
Consider the following problem: You want
to make sure that no one can insert rows in the weather table that do not have a matching entry
in the cities table. This is called maintaining the referential integrity of your data. In simplistic
database systems this would be implemented (if at all) by first looking at the cities table to check
if a matching record exists, and then inserting or rejecting the new weather records. This approach
has a number of problems and is very inconvenient, so PostgreSQL can do this for you.
The new declaration of the tables would look like this:

CREATE TABLE cities (
city varchar(80) primary key,
location point
);

CREATE TABLE weather (
city varchar(80) references cities(city),
temp_lo int,
temp_hi int,
prcp real,
date date
);

Now try inserting an invalid record:

INSERT INTO weather VALUES (’Berkeley’, 45, 53, 0.0, ’1994-11-28’);
ERROR: insert or update on table "weather" violates foreign key constraint "weather_city_fkey"
DETAIL: Key (city)=(Berkeley) is not present in table "cities".

The behavior of foreign keys can be finely tuned to your application. Making correct use of
foreign keys will definitely improve the quality of your database applications, so you are strongly
encouraged to learn about them.


PostgreSQL has many features not touched upon in this tutorial introduction, which has been oriented
toward newer users of SQL. These features are discussed in more detail in the remainder of PostgreSQL 9.5.7 Documentation book.

Mojang on Minecraft: Pocket Edition

The developers of the LEGO-like smash discuss the bite-sized version's rise from an inauspicious start to phone and tablet glory. Minecraft is an absolute sensation of a game, having sold more than 54 million copies across platforms and spawning all sorts of officially licensed doodads—like branded LEGO sets, foam swords, and a seemingly endless array of t-shirts. Leading the pack in sales isn't the original PC version, nor any of the home console ports, which have collectively moved past the computer edition in total copies sold.
It makes sense on the surface: Pocket Edition is the lowest-priced version, and there are hundreds of millions of active devices that can run the game. But this is the same game that was critically shrugged off upon release less than three years ago, derided for being a hollow shell of the PC experience.
However, the Pocket Edition of today is significantly larger and more in-depth, and the recent Version 0.9 update added long-desired features like infinite worlds and explorable caves. Swedish indie studio Mojang continues to expand the game to make it bigger and better, and continue spreading the gospel of Minecraft to more and more players—and the developers aren't finished yet.

Office Online versus Google Apps

At this time, Microsoft and Google are locked in battle over free-for-personal-use productivity applications, there's no one best online suite; both are quite capable and both have some important limitations.

Microsoft, Google, and Apple all offer online productivity suites that are free for personal use. And unlike past "free" suites (yes, I mean you, Microsoft Works and MS Office Web Apps!), these latest online suites are all surprisingly capable — and getting better almost weekly. In this discussion, I compare Office Online and Google Apps: two suites experienced Windows and Office users are most likely to use. Apple's iWork for iCloud I'll save for another day. It represents an elegant evolution of the Office genre, but there's a significant learning curve for dyed-in-the-wool Office "Officionados."

In short, Office Online and Google Apps are both so good you might not need to buy a standalone version of Office or subscribe to Office 365. Really! Microsoft and Google have their own reasons for dangling freebies at Windows users — more about that later. But whatever their motivations, using online suites could mean more money in your pocket.

But before I get into the details, let's clarify some confusing terminology.

Four months ago, "Office Web Apps" was a backwater website that seemed to be a weak adjunct to the desktop version of Office. Then Microsoft launched Office Online and kicked the old website into the bit bucket.

Google's terminology is, if anything, even more convoluted than Microsoft's. (And you didn't think that was possible.) Officially, the suite I discuss in this article is known as Google Drive, though few people call it that. Google has, of course, a cloud-storage service known as "Google Drive" — it competes with Microsoft's OneDrive.

For reasons that defy logic, the productivity tools once known as Google Apps — Document, Spreadsheet, and Presentation — now live under the cloud-storage, Google Drive site (see Figure 2). In common parlance, "Google Docs" might refer to just the word processor — or it might refer to all three productivity apps. And "Google Drive" might — or might not — include the cloud-storage component.

Both Microsoft's and Google's online productivity apps run only in Web browsers. You don't install anything; simply fire up your browser, go to the appropriate site (office.com for Office Online and drive.google.com for Google Apps), sign in — and you're ready to rock and roll. You will, of course, need a Microsoft account for Office Online and a Google account for Google Drive. Both accounts are free.

Based on my extensive testing, there's almost no difference in running any of the apps in Chrome, Firefox, or Internet Explorer — or on a wide variety of platforms that included Win7, Win8, OS X, iOS, and Android.

Office Online is free for personal use and for some organizations (Office 365 for Nonprofits). For businesses, it starts at U.S. $60 per year/per person (Office 365 Small Business) and goes up from there (more info).

Google Apps/Drive is also free for personal use. The cost for organizations ranges from free (nonprofit and educational) to $50 per year/per person for Google Apps for Business (more info).

Both Office Online and Google Apps have familiar productivity-suite interfaces that aren't amenable to touch-based tablet/small-screen use. In fact, Google Apps looks a lot like Office 2003.

Set up sharing for specific files and folders in Win 7 and Win 8

I generally don't use Windows' default data folders; I find it more convenient to create my own hierarchy of folders. (Keep in mind that custom folders should be included in a library, if you want them automatically backed up by Win8's File History.) Fortunately, it's relatively easy to designate individual folders, subfolders, and files for sharing with yourself, a homegroup, or other specific users.

If you're using Win8, just highlight the folder in File Explorer and select the Share tab. Win7's Windows Explorer doesn't have tabs, but you can access most of the same functions by clicking on the Share with option in Explorer's menu bar. (With either operating system, right-clicking the folder gives you most of the same options.)

In Win8, the Share tab will list other user accounts on the local system. Select a specific user or scroll down to Specific people, which opens the File Sharing dialog box. You can also select the homegroup if one is enabled. In the homegroup entry, choosing either the view or view and edit option (see Figure 5) lets you allow or prevent others from making changes to the file or folder.

Figure 5. Windows 8's new Share tab in File Explorer makes it easier to select folders and files for sharing.

In Win7, click Share with and then Specific people; that opens a File Sharing dialog box similar to Win8's.

Curiously, if you want to make a specific folder (other than the Public folder) available to everyone on your network, you must jump through a few extra hoops. You must open the file/folder's properties, select the Sharing tab, and click Share.

Win8 gives other sharing options. Under the Share tab, you'll find options for emailing, printing, faxing, or zipping a file or folder plus burning it to disc. (See Figure 5).

In Win8, if you want to refine control over who can do what with the contents of a shared file/folder, select the Advanced security option from the Share tab. The Advanced Security Settings utility will open, as shown in Figure 6.

Figure 6. Win8's Advanced Security Settings tool lets you set file- and folder-sharing privileges for individual users.

Double-click a specific user, and the Permission Entry dialog box opens (see Figure 7.) You can also specify, among other things, whether those permissions also extend to subfolders and files in those subfolders.

Figure 7. The advanced permissions dialog box lets you specify what a specific user can do with selected folders and files.

Win7 also lets you set specific user permissions for files and folders, but you'll find them in a different place. Right-click the folder and select Properties, then click either the Sharing or Security tabs. Under Sharing, select Advanced Sharing and then Permissions. In the Security tab, highlight the user and then click Edit. (This also works in Windows 8.)

Setting up shared files and folders manually takes some work, but it obviously lets you finely control who can access — and to what level — the data on a PC. When you want to access something shared on another computer, you simply go to the Network section of Windows/File Explorer's navigation panel, click the Expand button for a specific system, and open the file/folder you're looking for.

 

Amazon Fire Features : Firefly and Dynamic Perspective

In announcing Fire, its first-ever smartphone, Amazon showed off some sparks of innovation. There are two standout features in particular: a service called Firefly that can identify everything from a song to box of cereal to a Picasso painting based on how something looks or sounds; and a technology Amazon calls Dynamic Perspective that adjusts what users can see as they tilt or move the phone.

As cool as those innovations are, they likely won't be enough to overcome the daunting obstacles the Fire will face, especially against Apple's iPhones and smartphones powered by Google's Android. This Fire is more likely to burn out than to become a blazing success.

At first glance, the Fire appears unremarkable, a typical black slab with a nearly 5-inch screen.
But Amazon hopes some of the phone's unique features will set it apart. The Fire runs Fire OS, Amazon's version of Android that it also uses on its Kindle Fire tablets, which puts content like movies and music on an equal footing with applications.

Amazon is also throwing in two free services - a year's subscription to its Prime streaming music and video offering and free photo storage on its servers of all the pictures users take with the Fire. And that's a reminder that Amazon likely sees this as more than a phone, but as a way to get its customers to buy more stuff from its online store.

The Fire also comes with Amazon's Mayday feature. Users needing help with their phone can push the virtual Mayday button and be connected quickly with an Amazon customer support representative. It's kind of like a virtual version of Apple's Genius bars.

But the really unique features of the Fire are Firefly and Dynamic Perspective. Users activate Firefly by pressing a button on the side of the Fire. The app can identify songs, TV shows and movies just by listening to them and can use that information to provide details about them from sources like Amazon's own IMDb. It can identify products by their packaging or by their bar codes, allowing users to instantly check their prices and order them via Amazon.

Firefly can also recognize phone numbers and email addresses printed on posters or written on scraps of paper, allowing users to call them or save them to their address book without typing them in.

Firefly has a lot of cool potential in part because Amazon is allowing outside software developers to tap into its underlying technology. At the launch event Wednesday, for example, company CEO Jeff Bezos showed how the MyFitnessPal app could use the Firefly technology to look up nutrition information for a bag of Cheetos simply from a picture of the cheese-flavored snack food.

The Dynamic Perspective feature is a touchless gesture system combined with a kind of 3D-viewing effect. With Dynamic Perspective, users can access menus, shortcuts or additional information by simply tilting the screen left or right, rather than by swiping across it. In some cases, the phone also uses those motions to change what it displays on its screen, an effect that looks a lot like one on Apple's iPhone 5S that shifts the wallpaper on that phone's home screen as you tilt it.

As cool as Firefly and Dynamic Perspective may be, I don't think they'll be enough to make it a hit product. It just has too much going against it.

The smartphone market is dominated to such an extent by Samsung and Apple that it's been extraordinarily difficult for other companies - even those with a long history in making phones - to gain any traction, despite whatever cool innovations they may offer.

Meanwhile, the Fire is going to be hobbled by several factors. One is its price. At $200 for the base model with a two-year contract, the Fire costs the same as an iPhone 5S or a Samsung Galaxy S5, two of the leading phones on the market. And pre-announcement rumors aside, Amazon isn't offering any kind of deal on your cellphone subscription. So there's no financial incentive for the legions of iPhone or Samsung fans to switch to the Fire.

The Fire is also likely to be hobbled by the fact that it can't access the Google Play store, which has far more apps than Amazon's own app store. More importantly, Amazon does not offer Google's own apps, including Google Maps and the official version of Gmail. So, if you use Google services - and who doesn't? - the Fire may not be for you.