Ever find out about something that's been available to you for years, which would have made major portions of your job significantly easier, had you just known about it earlier? That's how I felt last summer when I found out about analytic functions during a presentation at the ODTUG Kaleidoscope conference. Now that I know about them, I've been finding all sorts of areas to use them–they're that useful.
Simply put, analytic functions allow you to do things in normal SQL that go far beyond what you normally can. Most of them could be replicated in PL/SQL (or even normal SQL, with convoluted subqueries and joins), but at a significant performance hit. The standard example is the “running balance”, like what you get on your bank statement:
Note that the “Balance” column is based on the values in the previous rows, something that isn't possible in normal SQL. Well, at least, not without jumping through hoops and collecting the data at least twice. Enter analytic functions! With them, it's easy to get what we want; the above example could be produced with something like this:
select trx_date as date, trx_amt as amount, sum(trx_amt) over (order by trx_date rows unbounded preceding) as balance from tbl where...
Straightforward, but very powerful, especially when you start nesting them in views (implicit or explicit). I use them somewhat extensively in WriteTrack, and yesterday had to pull them out again to build a tool which calculates the date we'll run out of stock based on our current monthly forecasts (each month has a different forecast amount, and the current stock can span one or more months–hey, it's a running total again!)
One of the best resources I've found is this page, by Shouvik Basu. It's seven years(!) old, but don't let that stop you. From what I understand, analytic functions were introduced in Oracle 8i, and have since been adopted into the SQL standard; I believe that they're now available in most databases.