Ron and Ella Wiki Page

Extremely Serious

Page 19 of 33

Ranking Window Functions

April 8, 2020 / / 0 Comments

CUME_DIST()

For SQL Server, this function calculates the cumulative distribution of a value within a group of values. In other words, CUME_DIST calculates the relative position of a specified value in a group of values. Assuming ascending ordering, the CUME_DIST of a value in row r is defined as the number of rows with values less than or equal to that value in row r, divided by the number of rows evaluated in the partition or query result set

DENSE_RANK()

This function returns the rank of each row within a result set partition, with no gaps in the ranking values. The rank of a specific row is one plus the number of distinct rank values that come before that specific row.

NTILE()

Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

PERCENT_RANK()

Calculates the relative rank of a row within a group of rows in SQL Server 2019 (15.x). Use PERCENT_RANK to evaluate the relative standing of a value within a query result set or partition. PERCENT_RANK is similar to the CUME_DIST function.

RANK()

Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.

ROW_NUMBER()

Numbers the output of a result set. More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Common Syntax

window_function () OVER clause

Aggregate Window Functions

April 8, 2020 / / 0 Comments

AVG()

This function returns the average of the values in a group

COUNT()

This function returns the number of items found in a group.

MAX()

Returns the maximum value in the expression.

MIN()

Returns the minimum value in the expression

STDEV()

Returns the statistical standard deviation of all values in the specified expression.

STDEVP()

Returns the statistical standard deviation for the population for all values in the specified expression.

SUM()

Returns the sum of all the values, or only the DISTINCT values, in the expression.

VAR()

Returns the statistical variance of all values in the specified expression

VARP()

Returns the statistical variance for the population for all values in the specified expression.

Common Syntax

window_function ( [ ALL ] expression ) 
OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list frame_clause ] )

Set the SSL Backend to use with Git

April 7, 2020 / / 0 Comments

To provide which ssl backend to use with git use the following syntax:

git config --global http.sslBackend <SSL_BACKEND>

The SSL_BACKEND token can potentially be openssl or schannel.

schannel uses the windows certicate store.

Example

git config --global http.sslBackend schannel

Verbatim String Literal

April 6, 2020 / / 0 Comments

A double quoted string prefixed with an at (@) sign symbol will be interpreted verbatim.

Syntax

@"<STRING>"

The STRING token can have simple escaped sequences (e.g. \\ for backslash), hexadecimal escape sequence (i.e. \x<HEX_NUMBER>) and unicode escape sequences (i.e. \u<HEX_NUMBER>) that are interpreted literally. However, the "" will still be escaped an will produce a single double quote mark.

Verbatim string literal and string interpolation can be combined with the syntax as $@"<STRING>". Moreover, the {{ or }} will still be escaped.

Example

var string1 = "C:\\Windows\\System32"
var string2 = @"C:\Windows\System32"

Console.WriteLine(string1)
Console.WriteLine(string2)

The writelines will produce the same output.

String Interpolation

April 6, 2020 / / 0 Comments

A double quoted string prefixed with a dollar ($) sign symbol can includes interpolation expression.

Syntax

$"<STRING>"

The STRING token can have interpolation expression and has the following syntax:

{<INTERPOLATION_EXPRESSION>[,<ALIGNMENT>][:<FORMAT_STRING>]}
Token Description
INTERPOLATION_EXPRESSION The part that will produce a formatted output.
ALIGNMENT Imposed a minimum number of character. If positive it is right-aligned and if negative it is left-aligned.
FORMAT_STRING A format that is supported by the expression. Find more on here.

If you needed to include { or } in your string output, escape it as {{ or }} respectively on which ever you need.

Example

var name = "World"
var greeting = $"Hello {name}"

Sample Usage of Teeing Collector

March 25, 2020 / / 0 Comments 
//A Stream of Integers
var ints = Stream.of(10, 20, 30 , 40);

//Calculate the average using the Collectors.teeing method.
long average = ints.collect(
        Collectors.teeing(
                
                //Sum all of the integers in the stream.
                Collectors.summingInt(Integer::valueOf),
                
                //Count the content of the stream.
                Collectors.counting(),
                
                //Calculate the average.
                ( sum, count) -> sum / count
        )
);

System.out.println(average);

Sample HttpClient.sendAsync() Method Usage

March 25, 2020 / / 0 Comments 
//Create an instance of HttpClient using its builder.
HttpClient httpClient = HttpClient.newBuilder()
        .version(HttpClient.Version.HTTP_2)
        .build();

//Create an instance of HttpRequest using its builder.
HttpRequest req = HttpRequest.newBuilder(URI.create("https://www.google.com"))
            .GET()
            .build();

/* Use the httpClient.sendAsync() method and encapsulate the response
in the CompletableFuture. */
CompletableFuture<HttpResponse> resFuture =
        httpClient.sendAsync(req, HttpResponse.BodyHandlers.ofString());

//Use the resFuture.thenAccept to wait for the async response.
resFuture.thenAccept(res -> System.out.println(res.version()));

//Wait for the resFuture to to complete.
resFuture.join();

Java Switch Expression

March 24, 2020 / / 0 Comments

Syntax

switch(VARIABLE) {
	case MATCH_1 [, MATCH_2][, MATCH_N] -> EXPRESSION; | THROW-STATEMENT; | BLOCK
	default -> EXPRESSION; | THROW-STATEMENT; | BLOCK
}
Token Description
VARIABLE The variable to test.
MATCH_1, MATCH_2, MATCH_N The value or values that can match the VARIABLE.
EXPRESSION A expression to execute.
THROW-STATEMENT A expression that throws an exception.
BLOCK A block of statements to execute.

If a return value is required use the yield keyword instead of return.

Example

public enum Day { 
	SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY; 
}

Day day = Day.WEDNESDAY;

int numLetters = switch (day) {
    case MONDAY, FRIDAY, SUNDAY -> 6;
    case TUESDAY -> 7;
    case THURSDAY, SATURDAY -> {
        System.out.println(8);
        yield 8;
    }
    case WEDNESDAY -> {
        System.out.println(9);
        yield 9;
    }
    default -> {
        throw new IllegalStateException("Invalid day: " + day);
    }
};

System.out.println(numLetters);
« Older posts Newer posts »

© 2025 Ron and Ella Wiki Page

Theme by Anders Noren — Up ↑