<edit>Just noticed Moodflow had already sused this technique</edit>
Well I thought I would take a break from my canyon project and have a go at some thing thats been a problem for a while now. I don't know if this has been posted before, my apologies if it has.
Transparent Water ~ The solution (well maybe lol)
Make two renders, one with the water and one without.
Open both in Photoshop and copy the water render over the bare render.
Mask out the water and delete the "shore line" in your upper layer.
Apply a layer mask to the selection in your upper layer and fill with a gradient black to white gradient.
Thats pretty much it, the water is now more transparent where the mask is darker and more opaque where it is lighter.
The two wavey examples below also used a greyscale copy of the water layer to further mask the alpha so that water more perpendicular to the viewer was also more transparent.
As a further step you can use the same selection to apply a slight ripple/distortion to the surface below the water.
I'm sure that this technique could be adapted to more complex environments, possibly with the use of a z depth layer as well
Richard