Search This Blog

Loading...

Thursday, May 26, 2016

MS SQL Server - Window Functions

A window function is one that can be applied to a set of rows, The word ‘window’ is used to refer to the set of rows that the function works on. It provides a way to work with data grouped into logical windows.

The OVER clause used to define a window or set of rows within a query result set. Then we use a window function to computes a value for each row in the subset. OVER clause can be used to compute aggregated values such as average, sum, min, max, or a top N per group results.

The window function can be of two types:

  • Ranking function
  • Aggregate function.

In this post, I will discuss Ranking Window Functions.

Depending of on whether you’re using a ranking window function or aggregate window function, the OVER clause will need to be implemented accordingly. If we use the OVER clause with a ranking function, we can specify an optional PARTITION BY clause and a required ORDER BY clause. However, if we are working with with an aggregate function, then a PARTITION BY clause must be used, and should not have an ORDER BY clause.

Working with Ranking Window Functions

Ranking functions return an integer ranking value for each row or group in a partition/window.

Transact-SQL provides the following ranking functions:

  • ROW_NUMBER
  • RANK
  • DENSE_RANK
  • NTILE

Let see each function in action with an example.

  • ROW_NUMBER:

    Returns a sequential integer number for each row in the window.

    Example: Create a sample table and insert some records to query.

    declare @tblItems table (
     ItemCode varchar(5),
     ItemCategory varchar(50),
     StockQty int
    )
    
    insert into @tblItems(ItemCode, ItemCategory, StockQty)
    values 
    ('I001', 'Books', 10),
    ('I002', 'Books', 20),
    ('I003', 'Laptops', 25),
    ('I004', 'Laptops', 30),
    ('I005', 'Laptops', 20),
    ('I006', 'SmartPhones', 50),
    ('I007', 'SmartPhones', 30),
    ('I008', 'Books', 10)
    

    The following example calculates a row number for @tblTest based on ItemCode.

    SELECT 
     Itemcode
    ,ROW_NUMBER() OVER(ORDER BY itemcode) AS 'ROW_NUMBER()'
    FROM @tblItems
    

    Here is the output:

  • RANK:

    Define an integral rank value for each row in the window. It returns a same rank value for repeated column values, however, the next number in the ranking sequence is skipped by the number of repeating rows (also known as gap). It does not always return consecutive integers.

    The following example ranks the @tblItems with sort by ItemCategory. Since the ItemCodes I001, I002, I008 have same ItemCategory, these are tied, these are ranked one, and the next rank for item I003 is returned as 4, i.e. skipped the values 2 and 3 from the previous tied items.

    SELECT 
     ItemCode
    ,ItemCategory
    ,RANK() OVER (ORDER BY ItemCategory) AS 'RANK()'
    FROM @tblItems
    

    Here is the output:

  • DENSE_RANK:

    Define an integral rank value for each row in the window, without any gaps in the ranking. It also returns a same rank value for repeated column values. If values in the ranking column are the same, they receive the same rank. The next number in the ranking sequence is the next number in the sequence. The numbers returned by the this function do not have gaps and always have consecutive ranks.

    The following example ranks the @tblItems with sort by ItemCategory. Since the ItemCodes I001, I002, I008 have same ItemCategory, these are tied, these are ranked one, and the next rank for item I003 is returned as 2, i.e. it does not skipped the gap values (2 and 3) from the previous tied items.

    SELECT 
     ItemCode
    ,ItemCategory
    ,DENSE_RANK() OVER (ORDER BY ItemCategory) AS 'DENSE_RANK()'
    FROM @tblItems
    

    Here is the output:

  • NTILE:

    Divides the result set into the number of groups specified as an argument to the function. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

    The following example divides rows into 3 groups of items based on ItemCategory. Because the total number of rows is not divisible by the number of groups, the first two groups have 3 rows and the remaining groups have 2 rows each.

    SELECT 
     ItemCode
    ,ItemCategory
    ,NTILE(3) OVER(ORDER BY ItemCategory) AS 'NTILE(3)' 
    FROM @tblItems
    

    Here is the output:

    Let see another example of NTILE with group argument as 4. The following example divides rows into 4 groups of items based on ItemCategory. Now the total number of rows is divisible by the number of groups, so each group has equal number of rows (2 rows in each group).

    SELECT 
     ItemCode
    ,ItemCategory
    ,NTILE(4) OVER(ORDER BY ItemCategory) AS 'NTILE(4)' 
    FROM @tblItems
    

    Here is the output:

References:

Monday, April 11, 2016

TypeScript - How to import other TypeScript files.

As you proceed using TypeScript in large projects, you will end up with multiple typescript files. Then you may find a problem how we can use/import functions/classes from other files.

For example we have following interface in IShape.ts file.

interface IShape {
        x: number;
        y: number;
        height: number;
        width: number;
    }

And in another file Square.ts we have Square class with one of the constructor overloads is accepting that IShape interface as parameter.

class Square {
        public x: number;
        public y: number;
        public height: number;
        public width: number;

        constructor();
        constructor(obj: IShape);
        constructor(obj?: any) {
            this.x = obj && obj.x || 0
            this.y = obj && obj.y || 0
            this.height = obj && obj.height || 0
            this.width = obj && obj.width || 0;
        }

But if you try to compile this, you may get the following error:

Error TS2304 Cannot find name 'IShape'.

Because Square.ts don't know about ISquare.ts. To fix this error, we have to add reference to ISquare.ts file on the top of Square.ts.

/// 

class Square {
        public x: number;
        public y: number;
        public height: number;
        public width: number;

        constructor();
        constructor(obj: IShape);
        constructor(obj?: any) {
            this.x = obj && obj.x || 0
            this.y = obj && obj.y || 0
            this.height = obj && obj.height || 0
            this.width = obj && obj.width || 0;
        }

Now it will compile successfully.

You can add multiple lines of references to add multiple files. Just remember that path is relative to the current file.

Monday, March 28, 2016

TypeScript - Namespaces

TypeScript allows to define namespaces for logical grouping of code. Older versions of typescript use the keyword module while newer version can also used the keyword namespace which can be used in place of module keyword. Lets see namespaces in action in following example.
We have defined a class MySquare in namespace ShapesNamespace.
namespace ShapesNamespace {
    export class MySquare {
        public x: number;
        public y: number;
        public height: number;
        public width: number;
    }
}
Where namespace keyword defined namespace scope. Also notice the keyword export before the keyword class, it will expose this class to become accessible from outside of ShapesNamespace. If you do not add export keyword, you can not access this class outside of ShapesNamespace.
Now let use this class in another namespace.
namespace AnotherNamespace {
    var square1 = new ShapesNamespace.MySquare();
    square1.height = 10;
}
Here we are accessing MySquare class from AnotherNamespace with fully qualified class name. Intellisense will show available items in the code.
Another way to use code from other namespaces is to use aliases. Let see how we can use short names as aliases instead of typing more text in fully qualified names again and again.
namespace AnotherNamespace {
    import MySquareAlias = ShapesNamespace.MySquare;
    var square1 = new MySquareAlias();
    square1.height = 10;
}
We used the import keyword to set alias of ShapesNamespace.MySquare class, when we need to access that class, we can simply use its alias MySquareAlias instead of typing full name. Also intellisense works as expected.

Monday, March 14, 2016

TypeScript - Function / Constructor Overloading

TypeScript supports overloading of functions/constructor, but not in the same way as in .Net. Since JavaScript does not provide support of overloading itself, TypeScript enforces strong typing against the various overload signatures at design time, the function name is the same, but the signature of each overload (i.e. their name and parameters, but not the return type) is different. But we still have to write a generalized function that could work in generated JavaScript code, that is compatible with all overloads signatures, and will have only one function implementation.

This example make it clear:

interface IShape {
        x: number;
        y: number;
        height: number;
        width: number;
    }

    class Square {
        public x: number;
        public y: number;
        public height: number;
        public width: number;

        constructor();
        constructor(obj: IShape);
        constructor(obj?: any) {
            this.x = obj && obj.x || 0
            this.y = obj && obj.y || 0
            this.height = obj && obj.height || 0
            this.width = obj && obj.width || 0;
        }
    }

Square class has total 3 constructor signatures, first one is empty, second contains an object of type implementing interface IShape, and the last overload receives parameter with type any to make it compatible with previous overloads. Also notice that only the last overload function signature could have the actual implementation code.

In the calling stack, it looks at the overload list, and check the first overload to call the function with the provided parameters. If it finds a match, it picks this overload as the correct overload. For this reason, usually overloads should be ordered from most specific to least specific.

You can create objects with this class in the following ways:

    //empty constructor
    var square1: Square;
    square1 = new Square();
    square1.x = 10;
    square1.y = 50;
    square1.height = 100;
    square1.width = 100;
    
    var squareConfig: IShape;
    squareConfig = { x: 10, y: 50, height: 100, width: 100 };

    //contstructor with interface IShap
    var square2: Square;
    square2 = new Square(squareConfig);

    //contstructor with JavaScript object literal
    var square3: Square;
    square3 = new Square({ x: 10, y: 50, height: 100, width: 100 });

If you are working in Visual Studio with TypeScript support enabled, you will get intellicense support, and display avialable overloads similar to this:

Empty Constructor
Constructor with IShape parameter

Following function overloads added to the Square class, having different parameters of basic types.

 SetColor(x:boolean) : void;
 SetColor(x: number): void;
 SetColor(x: string): void;
 SetColor(x: any): void
 {
  if (typeof x == "boolean"){
   alert("Boolean overload is called, use default color value.");
  }
  else if (typeof x == "number") {
   alert("Number overload is called, use color from number value.");
  }
  else if (typeof x == "string") {
   alert("Number overload is called, use color from string value.");
  }
 }

Since we could have only one real function implementation, so inside this function body, we have to check for different types in order to respond with desired behavior.

Visual Studio Intellicense support will display overload options similar to this:

Overload with Boolean type parameter
Overload with Number type parameter
Overload with String type parameter

Above function can be called in the following ways:

    square1.SetColor(true);
    square1.SetColor(255);
    square1.SetColor("#235253");

Wednesday, March 9, 2016

TypeScript: cast HTMLElement - using function document.getElementsByTagName()

While working with document.getElementsByTagName() in TypeScript, I faced problems in casting. Finally I make it work after searcing different sites, and concluded those things in this post that could help someone facing the same problem.

Function document.getElementsByTagName() returns NodeListOf<Element>, Where Element is more generic type extends from type Node. Since TypeScript used lib.d.ts declaration file to recognize native javascript functions and properties, you can find this function's multiple signatures in lib.d.ts with different tag names, each have different return type. For example, document.getElementsByTagName("h1") will return NodeListOf<HTMLHeadingElement>.

Here is the list of some common html elements with corresponding varabiles casts.

 var nodeList1: NodeListOf = document.getElementsByTagName("h1");
 var nodeList2: NodeListOf = document.getElementsByTagName("p");
 var nodeList3: NodeListOf = document.getElementsByTagName("div");
 var nodeList4: NodeListOf = document.getElementsByTagName("a");
 var nodeList5: NodeListOf = document.getElementsByTagName("input");

NodeList is an array-like structure containing multiple items of Node, you can access individual item by passing a particular index. For example, following example will return the first element in the nodelist with tag name h1.

 var var1: HTMLHeadingElement = document.getElementsByTagName("h1")[0];

In inheritance tree of the Document Object Model in lib.d.ts file, you can find that Element extends the Node type, acting as more generic type. Which further extended by HTMLElement, and then all major elements, including in above listing, will extend from HTMLElement.

Same like C# (or other Object-Oriented Languages), you have to take care of proper inheritance structure while working with elements casting. Consider these variables:

 var var1: Element = document.getElementsByTagName("h1")[0];
 var var2: HTMLElement = document.getElementsByTagName("h1")[0];
 var var3: HTMLHeadingElement = document.getElementsByTagName("h1")[0];

If you try to assign var2 to var1:

    var1 = var2;

it will be ok, since HTMLElement extends from Element. But if you try to assign var1 to var2:

    var2 = var1;

you will get compile error as below:

 TS2322 (Error Code)
 Type 'Element' is not assignable to type 'HTMLElement'. Property 'accessKey' is missing in type 'Element'.
 
 //or in generic form, if you try similar thing with other elements.
 Type 'x' is not assignable to type 'y'. Property 'z' is missing in type 'x'.

Because compiler is looking for target type's properties, if any property is missing in source type, it will prompt similar error message showing the name of the missing property.

The function document.getElementsByTagName() always returns NodeListOf<T>, If you want to work on array, you can cast this to array in the following statement:

 var nodeList: HTMLElement[] = (<HTMLElement[]> <any> document.getElementsByTagName("h1"));

This returns all h1 elements in variable nodeList, the array of HTMLElement.

Or you can also iterate through nodeList array items in for loop. In the following example, it will iterate through all items in nodeList and add these items to a new array.

 var nodeList: NodeListOf = document.getElementsByTagName("h1");
 var elementList : Array = [];

 if (nodeList) {
  for (let i = 0; i < nodeList.length; i++) {
   
   let node : Node = nodeList[i];
   elementList.push(node as HTMLElement);
  }
 }

References:

http://www.typescriptlang.org