OVER(PARTITION BY clause ORDER BY clause)

From MS Document,

PARTITION BY
Divides the query result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.

First of all, we might consider that the concept of PARTITION BY is similar with GROUP BY, but they are used in different situation.

We will do the following experiment steps by steps to illustrate its difference.

Preparation: Create testing data in Database:

CREATE TABLE People

(

  PeopleID int identity(1,1),

  Name varchar(20),

);

CREATE TABLE PaymentHistory

(

  PaymentHistoryID int identity(1,1),

  PeopleID int,

  PaymentDate Datetime,

  Amount decimal

);

INSERT INTO People

Select 'Frank' UNION ALL

Select 'Jake' UNION ALL

Select 'Jordan'

INSERT INTO PaymentHistory

Select 1, '2018-10-10', 100 UNION ALL

Select 1, '2018-10-11', 200 UNION ALL

Select 1, '2018-10-12', 300 UNION ALL

Select 2, '2018-10-09', 200 UNION ALL

Select 2, '2018-10-13', 300 UNION ALL

Select 3, '2018-10-14', 300 

First, we use the following query to view all the data.

select

       p.PeopleID,

       p.Name,

       ph.PaymentDate,

       ph.Amount

from dbo.People p

join dbo.PaymentHistory ph on p.PeopleID = ph.PeopleID

From results, we can see that each People has paid different amounts on different dates.

To image that if one day, clients want us to provide a report indicating each people’s last payment date? How do we handle it?

We might come up the following solution using GROUP BY.

select

       p.PeopleID,

       p.Name,

       Max(ph.PaymentDate) as LastPaymentDate

from dbo.People p

join dbo.PaymentHistory ph on p.PeopleID = ph.PeopleID

group by p.PeopleID, p.Name

According to the results, we can find that the row number is reduced from 6 rows to 3 rows. Group By commands group that 6 rows to 3, and use aggregate function Max to get the latest payment date.

But, if clients change their mind, they also want to know what amount of the last payment for each people? How do we modify the previous query?

Because GROUP BY will make rows become grouped into a single row, we will not know which information from that grouped row is the right one we want.

Now, you might realize that if we still want to keep modifying the previous query, it will become really complicated.

Then, we would like to introduce the ranking window functions, such as ROW_NUMBER().

Syntax:

ROW_NUMBER () OVER (PARTITION BY x ORDER BY y)

The following query is an example.

select

       p.PeopleID,

       p.Name,

       ph.PaymentDate,

       ph.Amount,

       ROW_NUMBER() over (Partition by p.PeopleID order by ph.PaymentDate desc) as RowNumber

from dbo.People p

join dbo.PaymentHistory ph on p.PeopleID = ph.PeopleID

We found that it provides rolled-up data without rolling up. Then, using RowNumber, we can select the amount of last payment date for each People.

select

       *

from (

       select

              p.PeopleID,

              p.Name,

              ph.PaymentDate,

              ph.Amount,

              Rank() over (Partition by p.PeopleID order by ph.PaymentDate desc) as RowNumber

       from dbo.People p

       join dbo.PaymentHistory ph on p.PeopleID = ph.PeopleID

)as q

where q.RowNumber = 1

That is cool function, right? Next time, not only GROUP BY, but also we can have another choice OVER (PARTITION BY ORDER BY)

Open-question survey database design

Recently, my friends discussed with me for how to design a database for online Survey system.

First of all, we found that there are plenty of types of survey. 

To simplify it, we focus on the open question on this moment(no predefined options for answer, no conditional question order).

On this design,

1. We can define tons of questions and then user Survey_Question table to pick up suitable questions to the specific Survey.

2. Using Survay_Participant table, we can get all the participants through the survey Id.

3. On Answer table, because of the FK SurveyParticipantId, we can know this answer is made by which participant and for which survey. Also, this table is bound to the question that it answered for.

4. Note that, we only store answer text in Answer table because it is open question.

FullCalendar, the Event Calendar

Purpose:

Recently, clients want us to implement a calendar which support event functionality. After researching, we found that FullCalendar is very suitable.



 FullCalendar is open source and support drag-and-drop events functionality.



The document is well defined and the APIs are easy to use.

It also supports various views such as month, week, day, agenda and list view to fit clients' requirement.



Below is a simple example.

1. define HTML element

2. Initialize FullCalender with options
    Note: "evts" is the list of events including the title and event date

3. Result

Quill Rich Text Editor

Purpose:

Sometimes, clients may require us to provide a panel which can allow them to input some information in admin side. After that, we will display that input data in user side with the proper format.

But, they might frequently contact us for the changes of content or styling.

If the input UI we provided for them is powerful enough, things will get easier. They can input the content with styling by themselves. Then, in our part, we just display that formatted content to the user side. That is the win-win situation. We save our time, and clients get more control for the content input.


Quill is free and open source. And it has well-down documentations. It is easy to use it.


Let’s take a look for a simple implementation to know how to get the content of quill editor and set it back.


1. First, initialize quill




2. Then the quill rich text area will be generated with the theme “snow”.



3. If we input something and format it through toolbars, then we can see the special format of quill to understand how quill handle it.






4. Actually, we can convert it to string format and store it to our database




5. Then, if we want to display it to quill editor, we just convert it back to object and use quill API to set it to the editor’s content. Then, magic is happened.




6. In the end, you can see that the stored formatted context can be displayed properly on quill editor.

Entity Framework Code-First Migration

Purpose: Learn how to use migration history to control your database changes

1.Preparation for Setting Entity Framework, connection string, and relating database

        a. Created a ASP.NET MVC project and install Entity Framework

            Tools -> NuGet Package Manager -> Manage NuGet Packages for Solutions…

              

              

        b. Setup connection string

              

                 project -> right click -> Properties

        c. On "Settings" section, select Connection String on Type field, and edit Value…

           Then you can select the relating database to this project.

2. Create Model and setup DbContext

        a. Create a class which represents your table (Under Models folder).

                 For example, if we want to create a Person table, you can setup it as the following picture.

                 Note: Property name ends with Id will be considered as primary key in table. 

               

        b. Create our DbContext class which inherits DbContext of Entity Framework.

                Note: We use DbContext to coordinate Entity Framework Functionality.

                In addition, add the previous class to DbSet.

                Also, we can specific the database from customized connection string name.

                On this example, we named it “DefaultContext”

       

        

            

     

        c. Then, change the connection string to the name we specified on previous step

            

3. Migration

         

        a. Enable Migration for our project

                 Tools -> NuGet Package Manager -> Package Manager Console

         Run: enable-migrations

        b. Then, we can notice that in our project folder, the Migrations folder was created.

        c. Run: “add-migration init” for creating migration
            Note: "init" is the migration name in this example, you can use anything you want.

            After that, we can see that under the folder, the migration file called “xxxx_init.cs” was created

         

             Then, we can run "update-database" commands to change database.


        d. On SQL Server management, we can find that the Person table was created!
            In addition, this database includes the migration history table (Only One record.)

4. How to revert migrations

        a. Assume that we keep developing our system, and we want to modify person table for more information to meet clients’ requirement.
             For testing purpose in this example, in this step, we want to deliberately make a mistake for the typo issue. And do the Migration to update database!

                       

        b. On SQL Server Management, we can find that the Person table has been changed, and the new migration history has been inserted.

        c. On this moment, we might find that we make a typo mistake.

            

            We can run the following commands on Console to revert to the specific migration history record.

            

            Commands: update-databse -targetMigration init
 

             

        d. Then we can find that the database has been reverted to the first migration we created before. And the second migration history has been removed!

        e. Now, we can delete the wrong migration on our project folder.

            Then, we correct the properties of models (correct the typo). 

            In the end, add the new migration again.

         

        f. Then, we can check the database and migration history.

            Everything is in control now!