Need help in understanding a SELECT query

I have a following query. It uses only one table (Customers) from Northwind database. I completely have no idea how does it work, and what its intention is. I hope there is a lot of DBAs here so I ask for explanation. particularly don't know what the OVER and PARTITION does here.

WITH NumberedWom开发者_StackOverflowen AS
(
    SELECT CustomerId ,ROW_NUMBER() OVER 
            (
                PARTITION BY c.Country 
                ORDER BY LEN(c.CompanyName) ASC
            ) 
                women 
                FROM Customers c
)

SELECT * FROM NumberedWomen WHERE women > 3 

If you needed the db schema, it is here

---

This function:

ROW_NUMBER() OVER (PARTITION BY c.Country ORDER BY LEN(c.CompanyName) ASC) 

assigns continuous row numbers to records within each country, ordering the records by LEN(companyName).

If you have these data:

country   companyName
US        Apple
US        Google
UK        BAT
UK        BP
US        GM

, then the query will assign numbers from 1 and 3 to the US companies and 1 to 2 to UK companies, ordering them by the name length:

country   companyName   ROW_NUMBER()
US        GM            1
US        Apple         2
US        Google        3
UK        BP            1
UK        BAT           2

---

ROW_NUMBER() is a ranking function.

OVER tells it how to create rank numbers.

PARTITION BY [expression] tells the ROW_NUMBER function to restart ranking whenever [expression] contains a new value

In your case, for every country, a series of numbers starting with 1 is created. Within a country, the Companies are ordered by the length of their name (shorter name = lower rank).

The final query:

SELECT * FROM NumberedWomen WHERE women > 3

selects all customers except if the company-country combination is part of one of the companies with the 3 shortest names in the same country.