BigQuery: Calculate the MEDIAN in BigQuery – Pascal Landau

There is no MEDIAN() function in Google BigQuery, but we can still calculate the MEDIAN
with the
functions. The difference between those two functions is the linear interpolation that is applied
when using PERCENTILE_CONT(x, 0.5) – so that’s probably what you want when dealing with numeric
values. Take the numbers 1,2,3,4 for example:

  • PERCENTILE_CONT(x, 0.5) yields 2.5 (as the 50% percentile is exactly between 2 and 3)
  • PERCENTILE_DISC(x, 0.5) yields 2 (as the 50% percentile is >= 2)


SELECT PERCENTILE_CONT(x, 0.5) OVER() AS median_cont, PERCENTILE_DISC(x, 0.5) OVER() AS median_disc
FROM UNNEST([1,2,3,4]) as x


median_cont median_disc
2.5 2

Caution: As of today (2020-06-20), BigQuery only supports PERCENTILE_CONT and PERCENTILE_DISC for window functions
(hence the OVER() clause and the LIMIT 1 in the example above):

PERCENTILE_CONT is under development, and we will publish the documentation once it is GA. We will support it as analytic function first, and we plan to support it as aggregate function (allowing GROUP BY) later.

Source: SO: percentile functions with GROUPBY in BigQuery

The more common use case is probably to calculate the median as a result of a GROUP BY statement.
I.e. I would like to write something like this to get the median of quantity per product_id.

SELECT product_id, PERCENTILE_CONT(quantity, 0.5) AS median
GROUP BY product_id

Right now, that is only possible for the average via
but not for the median. But we can still work around that limitation by using the
PERCENTILE_CONT function on a window partitioned by product_id, then group by the product_id
(to get only one row per product_id)
and resolve a single median value via ANY_VALUE().

Working Example

<script src=””><script src=””>

Run on BigQuery

Open in BigQuery UI

BigQuery UI: MEDIAN in BigQuery example



There is also the APPROX_QUANTILES()
function (mentioned here) that can by applied to a GROUP BY. I didn’t have a practical use case for approximate functions
yet, though. Thus, I don’t know the implications of “not using an exact calculation” and rather mention
this for the sake of completeness. Example:

SELECT product_id, APPROX_QUANTILES(quantity, 100)[OFFSET(50)] as approx_median
GROUP BY product_id

PHP Internals News: Episode 58: Non-Capturing Catches – Derick Rethans

PHP Internals News: Episode 58: Non-Capturing Catches

In this episode of “PHP Internals News” I chat with Max Semenik (GitHub) about the Non-Capturing Catches RFC that he’s worked on, and that’s been accepted for PHP 8, as well as about bundling, or not, of extensions.

The RSS feed for this podcast is, you can download this episode’s MP3 file, and it’s available on Spotify and iTunes. There is a dedicated website:


Derick Rethans 0:18

Hi, I’m Derick, and this is PHP internals news, a weekly podcast dedicated to demystifying the development of the PHP language. This is Episode 58. Today I’m talking with Max Semenik about an RFC that is proposed called non capturing catches. Hello Max, would you please introduce yourself.

Max Semenik 0:38

Hi Derick. I’m an open source developer, working mostly on MediaWiki. So that’s how I came to be interested in contributing to PHP.

Derick Rethans 0:50

Have you been working with MediaWiki for a long time?

Max Semenik 0:53

Something like 11 years, I guess.

Derick Rethans 0:56

That sounds like a long time to me. The RFC that you’ve made. What is the problem that is trying to address?

Max Semenik 1:03

In current PHP, you have to specify a variable for exceptions you catch, even if I you don’t need to use this variable in your code, and I’m proposing to change it to allow people to just specify an exception type.

Derick Rethans 1:20

At the moment, the way how you catch an exception is by using catch, opening parenthesis, exception class, variable, and you’re saying that you don’t have to do the name of the variable any more. I get that right?

Max Semenik 1:33


Derick Rethans 1:34

Is that pretty much the only change that this is making?

Max Semenik 1:38

Yes, it’s a very small, and well defined RFC. I just wanted to do something small, as my start to contributing to PHP.

Derick Rethans 1:51

I’m reading the RFC, it states also that the what used to be an earlier RFC. How does that differ from the one that you’ve proposed?

Max Semenik 2:00

The previous RFC wanted to also permit a blanket catching of exceptions, as in anything. And that’s all, which, understandably, has caused some objections from the PHP community. While most people commented positively on the part that I’m proposing now. Or should I say really propose because the RFC, passed and was merged yesterday.

Derick Rethans 2:35

I had forgotten about it actually, it’s good that you reminded me. So yeah, it got merged and ready for PHP eight. Basically what you say you picked the non controversial parts of an early RFC?

Max Semenik 2:47

I actually chose something to contribute and then looked for an RFC, to see if it was discussed previously.

Derick Rethans 2:55

Oh, I see. So, your primary idea of wanting to contribute to PHP, instead of you having an itch that you wanted to scratch, it’s like you’re saying?

Max Semenik 3:04

I have way larger itches that I will scratch later when I will learn how to work with PHP’s code base which, which is really huge.

Truncated by Planet PHP, read more at the original (another 11910 bytes)

Book release: PHP for the Web – Matthias Noback

Book cover

While Advanced Web Application Architecture is still a work in progress, I decided to release another project in the meantime: a book for beginning PHP developers called PHP for the Web.

Of course, there are PHP books for beginners, but most of them aren’t very concise.
They cover many related topics like relational databases, CLI applications, and sending emails.
I wanted to write a book that only shows what makes PHP so special when it comes to web development.

Writing this book took me back to the beginning of my own programming career in 2002.
Many things have changed since then, but surprisingly many things are still the same too!

The biggest change when it comes to dealing with the web in PHP: we now have frameworks.
This is obviously a good thing, since it has made our applications much more secure.
For instance, in 2002 I wasn’t aware of the need for output escaping, or to escape parameters in my SQL queries, and I didn’t know that I should protect TinyMCE’s upload script against anonymous file uploads…

On the other hand frameworks hide so many of the lower-level details that I dare to say that many PHP developers today don’t know how it all works under the hood.
What are cookies and how do they work?
What’s the difference between PHP errors and exceptions?
What is $_FILES and how can I use it?

This new book, PHP for the Web explains it all in over 200 pages.

If you’re thinking about learning PHP to build web applications, use the following link to get the book for just $9:
And if you know someone who thinks about learning PHP, forward this link to them as well!

Here’s the table of contents to give you an idea of what’s in there.
If you want to read a sample, go to the landing page and click on Read Free Sample.

  1. Serving resources
    • Serving an index.html file with the built-in web server
    • The project root should not be the document root
    • Communication between the browser and the server
  2. Serving PHP scripts
    • The response: status, headers and body
    • Linking to other pages
    • Passing values between requests
    • user input can’t be trusted
  3. Forms
    • Submitting form data as query parameters
    • Always use output escaping
    • Adding a select element to the form
    • Submitting data via the request body
  4. Cookies
    • Setting a cookie
    • Using a cookie
    • Redirecting after processing a POST request
  5. Sessions
    • Session files and serialized data
    • Flash messages
    • Using flash messages everywhere
  6. Authentication
    • Setting up a login form
    • Logging out
  7. Project structure
    • Header and footer snippets
    • Bootstrapping
    • Routing
  8. CRUD part 1: Create
    • Saving JSON-encoded data in a file
    • Adding a tour
    • Form validation
    • Listing tours
  9. CRUD part 2: The rest
    • Introducing some reusable elements
    • Editing tour data
    • Deleting tours
  10. File uploads
    • Uploading a file
    • Showing the uploaded picture
    • Form validation for file uploads
  11. Error handling
    • Producing an error
    • Using different configuration settings in production
    • PHP errors
  12. Automated testing
    • Using Composer to install testing tools
    • Creating our first browser test
    • Starting with a clean slate
  13. Conclusion

Now go get your copy!

PHP Internals News: Episode 57: Conditional Codeflow Statements – Derick Rethans

PHP Internals News: Episode 57: Conditional Codeflow Statements

In this episode of “PHP Internals News” I chat with Ralph Schindler (Twitter, GitHub, Blog) about the Conditional Return, Break, and Continue Statements RFC that he’s proposed.

The RSS feed for this podcast is, you can download this episode’s MP3 file, and it’s available on Spotify and iTunes. There is a dedicated website:


Derick Rethans 0:17 Hi, I’m Derick, and this is PHP internals news, a weekly podcast dedicated to demystifying the development of the PHP language. This is Episode 57. Today I’m talking with Raluphl Schindler about an RFC that he’s proposing titled “Conditional return break and continue statements”. Hi Ralph, would you please introduce yourself.

Ralph Schindler 0:37 Hey, thanks for having me Derick. I am Ralph Schindler, just to give you a guess the 50,000 foot view of who I am. I’ve been doing PHP for 22 years now. Ever since the PHP three days, I worked in a number of companies in the industry. Before I broke out into the sort of knowing other PHP developers I was a solo practitioner. After that I went worked for three Comm. And that was kind of a big corporation after that I moved to Zend. I worked in the framework team at Zend and then after that, I worked for another company based out of Austin for friend of mine Josh Butts. That, we’ve been purchased since then by Ziff media. I’m still kind of in the corporate world. Ziff media owns some things you might have heard of, PC Magazine, Mashable, The company that owns us owns is called j two they are j facts. They keep buying companies, so it’s interesting I get to see a lot of different products and companies they get bought and they kind of get folded into the umbrella, and it’s, it’s an interesting place to work. I really enjoy it.

Derick Rethans 1:39 Very different from my non enterprise gigs

Ralph Schindler 1:43 Enterprise is such an abstract word, and, you know, it’s kind of everybody’s got different experiences with it.

Derick Rethans 1:49 Let’s dive straight into this RFC that you’re proposing. What is the problem that this RFC is trying to solve?

Ralph Schindler 1:54 This is actually kind of the bulk of what I want to talk about, because the actual implementation of it all is is extremely small. As it turns out it’s kind of a heated and divided topic, My Twitter blew up last weekend after I tweeted it out, and some other people retweeted it so it’s probably interesting. I really had to sit down and think about this one question you’ve got is what is it trying to solve. First and foremost, it’s something I’ve wanted for a really long time, a couple years.

Two weekends ago I sat down and it was a Saturday and I’m like, you know what I haven’t haven’t hacked on the PHP source in such a long time. The last thing I did was the colon colon class thing, and I was like seven or eight years ago. And again, I got into that because I really wanted the challenge of like digging into the lexer and all that stuff and, incidentally, you know, I load PHP source in Xcode, and my workflow is: I like to set breakpoints in things, and I like to run something, and I look in the memory and I see what’s going on and that’s how I learned about things. And so I wanted to do that again. And this seemed like a small enough project where I could say, you know this is something I want to see in language, let me see if I can hack it out. First and foremost, I want this. And, you know, that’s, it’s a simple thing.

So what is it exactly is, it’s basically at the statement level of PHP, it is a what they like to call a compound syntactic unit. Something that changes the statement in a way that I think probably facilitates more meaning and intent, and sometimes, not always, it’ll do that and fewer lines of code. To kind of expand on that, this is a bit of a joke but a couple

Truncated by Planet PHP, read more at the original (another 22164 bytes)

PHP 7.3.19 Released – PHP: Hypertext Preprocessor

The PHP development team announces the immediate availability of PHP 7.3.19. This is a bug fix release.All PHP 7.3 users are encouraged to upgrade to this version.For source downloads of PHP 7.3.19 please visit our downloads page, Windows source and binaries can be found on The list of changes is recorded in the ChangeLog.

Dumb Reasons to Hate PHP – Stephen Coakley

PHP just recently celebrated its 25th anniversary since it was first introduced, which is quite the achievement, considering it still powers a large slice of the Internet today. I don’t write much PHP anymore myself as I’ve more or less moved on to new and different things, but I am incredibly grateful to PHP. It was one of the first “real” programming languages I really invested in to learn programming, and learn I did. I built real things, real websites with it, and also was involved in the community for a while. I saw the rise of Composer and Packagist replace the aging PEAR. I saw the release of PHP 7 and all the work that went into it the years prior leading up to it.

Now as expected whenever talking about PHP on the Internet, people are quick to grab their pitchforks and rehash the same classic criticisms of PHP over and over like a mantra. Is it to feel superior? Do they think they’re doing a public service? I don’t know. What I do know is that they’re right to some extent; PHP isn’t the best-designed language by any means, largely because it changed organically and incrementally over time. It certainly hasn’t stopped PHP from becoming as popular as it has.

There are plenty of good reasons why PHP isn’t the best language for many use-cases, and reasons why other languages are superior. I consider myself very experienced with it, so I speak from experience. Here are some examples just from memory:

  • The standard library, while fairly complete, doesn’t really follow modern PHP’s own best practices for API design, as it was largely created before PHP had things like namespaces and classes. This results in an odd disconnect with modern packages, and that weird mix of styles never really goes away.
  • The standard library also cares a lot about backwards compatibility, which is a good thing, but its also a double-edged sword. There are a lot of APIs and extensions that are soft-deprecated or generally not used in favor of higher-quality third-party packages.
  • The fact that every class file begins with <?php reminds you that PHP was originally just an html preprocessor and always runs inside the context of another file format. It makes sense, but its unusual and weird, especially since embedding PHP into html isn’t even done at all in many frameworks which have dedicated templating languages instead.

There are probably others, but these don’t keep me from remembering PHP with fondness as something that just works out of the box and has a lot of convenient features for web development.

What’s strange to me though is that instead of reasonable complaints like these, people like to present complaints that don’t make sense, aren’t true, or are just plain silly. Let’s take a look at just a couple that I’ve seen.

The syntax is strange and archaic!

This complaint doesn’t really make much sense to me. PHP’s syntax is very heavily inspired by C (which it is written in) and borrows many things from it. In fact, it fits right in with most of the languages in the C family of syntax. Just swap the dot operator for -> (which by the way is also lifted from C, its equivalent to (*struct_ptr).field), prepend all your variables with the $ sigil, and that’s just about it. It’s got your boring traditional class syntax that even JavaScript adopted, closures, and pretty much every modern convenience.

Granted, sigils probably remind you of Perl, but don’t worry, they don’t have crazy effects on data types like in Perl. Just think of it as part of the variable name and you’ll be fine.

There are a few PHP-specific oddities in its syntax, like the @ operator and using \ as a namespace separator, but these seem like really petty nitpicks to me.

It isn’t modular!

This sort of complaint is really nebulous, and could stand to have some clarifying questions asked. Usually people mean one of two things:

  1. Everything is in a global namespace with no modular separation.
  2. There is no modular way of packaging code.

Now both of these are just blatantly false. The first one is easy: PHP has namespaces, like Java, C#, or what-have-you. And they were added to the language in version 5.3, which was released in 2009! Now to be fair, there still exists a lot of codebases that were initially designed before then (like WordPress) that don’t leverage namespaces everywhere because of this, and this includes the standard library itself. But generally namespaces have been adopted for some time, and any modern PHP codebase uses them well.

The second

Truncated by Planet PHP, read more at the original (another 5391 bytes)