logo
Tags down

shadow

Group specific number of rows together and generate a group id


By : DHARMENDRA
Date : October 14 2020, 02:15 PM
I hope this helps you . Try the following.
The -1 before the ROW_NUMBER is used so that the ROW_NUMBER values 1, 2 and 3 (which will be 0, 1 and 2) are grouped together when divided by 3, since the result is 0 (INT division).
code :
;WITH SampleData AS
(
    SELECT
        A.Roll,
        A.Subject,
        RowNumberBySubject = -1 + ROW_NUMBER() OVER (PARTITION BY A.Subject ORDER BY A.Roll ASC),
        MinRollBySubject = MIN(A.Roll) OVER (PARTITION BY A.Subject)
    FROM
        (VALUES
            (1,'Robots'),
            (2,'Robots'),
            (3,'Robots'),
            (4,'Robots'),
            (5,'Robots'),
            (6,'Space'),
            (7,'Space'),
            (8,'Space'),
            (9,'Space'),
            (10,'Neurons'),
            (11,'Neurons') 
            ) A(Roll, [Subject])
)
SELECT
    S.Roll,
    S.Subject,
    S.RowNumberBySubject,
    S.MinRollBySubject,
    GroupIDBySubject = S.RowNumberBySubject / 3,
    GroupId = DENSE_RANK() OVER (
        ORDER BY 
            S.MinRollBySubject, 
            S.RowNumberBySubject / 3) -- GroupIDBySubject
FROM
    SampleData AS S
ORDER BY
    S.Roll ASC
Roll    Subject RowNumberBySubject  MinRollBySubject    GroupIDBySubject    GroupId
1       Robots  0                   1                   0                   1
2       Robots  1                   1                   0                   1
3       Robots  2                   1                   0                   1
4       Robots  3                   1                   1                   2
5       Robots  4                   1                   1                   2
6       Space   0                   6                   0                   3
7       Space   1                   6                   0                   3
8       Space   2                   6                   0                   3
9       Space   3                   6                   1                   4
10      Neurons 0                   10                  0                   5
11      Neurons 1                   10                  0                   5


Share : facebook icon twitter icon

Getting a row number of a row which is associated with a specific group of rows


By : YusukeMy
Date : March 29 2020, 07:55 AM
This might help you I guess you need to count each group of PROD_ID separately. In this case you should add one more user defined variable to store previous PROD_ID and reset @rownum when new PROD_ID group starts.
code :
SELECT rownum,rating_id,prod_id 
FROM   (SELECT if(prod_id=@prev_prod,@rownum := @rownum + 1,@rownum:=1)
               AS rownum, 
               tbl.rating_id,
               tbl.prod_id,
               @prev_prod:=prod_id  
        FROM   rating tbl, 
               (SELECT @rownum := 1,
                       @prev_prod:=NULL)t 
        ORDER  BY tbl.prod_id)t 
WHERE  rating_id = ?

Group by multiple columns, get group total count and specific column from last two rows in each group


By : user56946
Date : March 29 2020, 07:55 AM
around this issue I have an SQL Server table with the following columns: , I would attempt this by using the following WITH clause:
code :
WITH RUL AS (
select
  UserId,
  Area,
  Action,
  ObjectId,
  RelatedUserLink as RelatedUserLink1,

  LAG(RelatedUserLink) OVER (PARTITION BY UserId, Area, Action, ObjectId ORDER BY Created) as RelatedUserLink2,

  ROW_NUMBER() OVER (PARTITION BY UserId, Area, Action, ObjectId ORDER BY Created DESC) latest_to_earliest,

  MAX(Created) OVER (PARTITION BY UserId, Area, Action, ObjectId) as Created,

  COUNT(*) OVER OVER (PARTITION BY UserId, Area, Action, ObjectId) as Count

from
  Notification
where UserId = 10
)
select 
  UserId,
  Area,
  Action,
  ObjectId,
  RelatedUserLink1,
  RelatedUserLink2,
  Created,
  Count
from 
  RUL 
where 
  latest_to_earliest = 1;

Group by specific value and generate sequence number using xslt 1.0


By : Boqian Wang
Date : March 29 2020, 07:55 AM
wish helps you I was creating template for Place structure as first stage to make code more clear, then template for creating structure per TravelAgency and pass them in main template with duplicates preventing, please see XSL below (with some explanation in comments):
code :
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"> 
    <xsl:output method="xml" />
    <!--create Place structure-->
    <xsl:template name="place">
        <xsl:param name="param.travel.id"/>
        <xsl:param name="param.travel.agency"/> 
        <xsl:param name="param.object.id"/>
        <xsl:param name="param.object.number"/>  
        <Place>                
            <UniqueTravelID>                    
                <xsl:value-of select="$param.travel.id"/>                
            </UniqueTravelID>                
            <TravelAgency>                    
                <xsl:value-of select="$param.travel.agency"/>                
            </TravelAgency>                
            <UniqueObjectiveID>                    
                <xsl:value-of select="$param.object.id"/>                                    
            </UniqueObjectiveID>                
            <ObjectiveNumber>                    
                <xsl:value-of select="$param.object.number"/>                
            </ObjectiveNumber>            
        </Place>        
    </xsl:template>    
    <!--create template with Place filter by Agency--> 
    <xsl:template name="filter-place">
        <xsl:param name="param.agency"/>   
        <xsl:for-each select="/HolidayPlan/Travel[TravelAgency = $param.agency]">
            <xsl:variable name="var.travel.id" select="/HolidayPlan/Travel[TravelAgency = $param.agency][1]/UniqueTravelID"/>
            <xsl:variable name="var.travel.agency" select="/HolidayPlan/Travel[TravelAgency = $param.agency][1]/TravelAgency"/>
            <xsl:variable name="var.prev.obj.count" select="count(preceding::Travel[TravelAgency = $param.agency]/IntermediateObjectives//UniqueObjectiveID[1])"/>                      
            <xsl:variable name="var.object.number" select="position() + $var.prev.obj.count"/>
            <!--create day counter from Day-->
            <xsl:variable name="var.day">                    
                <xsl:choose>                        
                    <xsl:when test="normalize-space(Day) = '1'">                            
                        <xsl:value-of select="Day"/>                        
                    </xsl:when>
                    <xsl:otherwise>
                        <xsl:value-of select="Day + $var.prev.obj.count"/>
                    </xsl:otherwise>
                </xsl:choose>                
            </xsl:variable>    
            <!--apply Place structure from Travel-->
            <xsl:call-template name="place">
                <xsl:with-param name="param.travel.id" select="$var.travel.id"/>
                <xsl:with-param name="param.travel.agency" select="$var.travel.agency"/>
                <xsl:with-param name="param.object.id" select="StartLocationID"/>
                <xsl:with-param name="param.object.number" select="$var.day"/>    
            </xsl:call-template>
            <!--apply Place structure from IntermediateObjectives-->            
            <xsl:if test="IntermediateObjectives/*">
                <xsl:for-each select="IntermediateObjectives/*">
                        <xsl:call-template name="place">
                            <xsl:with-param name="param.travel.id" select="$var.travel.id"/>
                            <xsl:with-param name="param.travel.agency" select="$var.travel.agency"/>
                            <xsl:with-param name="param.object.id" select="./UniqueObjectiveID"/>
                            <!--Day counter from IntermediateObjectives-->
                            <xsl:with-param name="param.object.number" select="$var.day + ./ObjectiveNumber"/>
                        </xsl:call-template>               
                </xsl:for-each>
            </xsl:if>
        </xsl:for-each>
        <!--create counter variables for last day-->
        <xsl:variable name="travel.count" select="count(/HolidayPlan/Travel[TravelAgency = $param.agency])" />
        <xsl:variable name="all.obj.count" select="count(/HolidayPlan/Travel[TravelAgency = $param.agency]/IntermediateObjectives/*/UniqueObjectiveID)" /> 
        <!--apply Place structure for last day-->                                       
        <xsl:call-template name="place">
            <xsl:with-param name="param.travel.id" select="/HolidayPlan/Travel[TravelAgency = $param.agency][1]/UniqueTravelID"/>
            <xsl:with-param name="param.travel.agency" select="/HolidayPlan/Travel[TravelAgency = $param.agency][1]/TravelAgency"/>
            <xsl:with-param name="param.object.id" select="/HolidayPlan/Travel[TravelAgency = $param.agency][position() = $travel.count]/EndLocationID"/>
            <!--calculation last counter as next Travel Days + Intermediate Objectives + First Day-->
            <xsl:with-param name="param.object.number" select="$travel.count + $all.obj.count + 1"/>
        </xsl:call-template>                                         
    </xsl:template>
    <!--main template - VisitedPlaces structure-->
    <xsl:template match="/"> 
        <VisitedPlaces>                                                               
            <xsl:for-each select="/HolidayPlan/Travel">
                <!--preventing TravelAgency values duplicates and pass to filter-place template as parameter-->            
                <xsl:if test="not(preceding::Travel[TravelAgency/text() = current()/TravelAgency/text()])">                                                    
                    <xsl:call-template name="filter-place">                                            
                        <xsl:with-param name="param.agency" select="./TravelAgency"/>                                    
                    </xsl:call-template>                          
                </xsl:if>                 
            </xsl:for-each>
        </VisitedPlaces>         
    </xsl:template>
</xsl:stylesheet>
<?xml version="1.0" encoding="UTF-8"?>
<VisitedPlaces>
    <Place>
        <UniqueTravelID>100-1</UniqueTravelID>
        <TravelAgency>Agency1</TravelAgency>
        <UniqueObjectiveID>A</UniqueObjectiveID>
        <ObjectiveNumber>1</ObjectiveNumber>
    </Place>
    <Place>
        <UniqueTravelID>100-1</UniqueTravelID>
        <TravelAgency>Agency1</TravelAgency>
        <UniqueObjectiveID>Objective1</UniqueObjectiveID>
        <ObjectiveNumber>2</ObjectiveNumber>
    </Place>
    <Place>
        <UniqueTravelID>100-1</UniqueTravelID>
        <TravelAgency>Agency1</TravelAgency>
        <UniqueObjectiveID>Objective2</UniqueObjectiveID>
        <ObjectiveNumber>3</ObjectiveNumber>
    </Place>
    <Place>
        <UniqueTravelID>100-1</UniqueTravelID>
        <TravelAgency>Agency1</TravelAgency>
        <UniqueObjectiveID>B</UniqueObjectiveID>
        <ObjectiveNumber>4</ObjectiveNumber>
    </Place>
    <Place>
        <UniqueTravelID>100-1</UniqueTravelID>
        <TravelAgency>Agency1</TravelAgency>
        <UniqueObjectiveID>Objective3</UniqueObjectiveID>
        <ObjectiveNumber>5</ObjectiveNumber>
    </Place>
    <Place>
        <UniqueTravelID>100-1</UniqueTravelID>
        <TravelAgency>Agency1</TravelAgency>
        <UniqueObjectiveID>B</UniqueObjectiveID>
        <ObjectiveNumber>6</ObjectiveNumber>
    </Place>
    <Place>
        <UniqueTravelID>100-1</UniqueTravelID>
        <TravelAgency>Agency1</TravelAgency>
        <UniqueObjectiveID>A</UniqueObjectiveID>
        <ObjectiveNumber>7</ObjectiveNumber>
    </Place>
    <Place>
        <UniqueTravelID>200-1</UniqueTravelID>
        <TravelAgency>Agency2</TravelAgency>
        <UniqueObjectiveID>C</UniqueObjectiveID>
        <ObjectiveNumber>1</ObjectiveNumber>
    </Place>
    <Place>
        <UniqueTravelID>200-1</UniqueTravelID>
        <TravelAgency>Agency2</TravelAgency>
        <UniqueObjectiveID>Objective1</UniqueObjectiveID>
        <ObjectiveNumber>2</ObjectiveNumber>
    </Place>
    <Place>
        <UniqueTravelID>200-1</UniqueTravelID>
        <TravelAgency>Agency2</TravelAgency>
        <UniqueObjectiveID>D</UniqueObjectiveID>
        <ObjectiveNumber>3</ObjectiveNumber>
    </Place>
    <Place>
        <UniqueTravelID>200-1</UniqueTravelID>
        <TravelAgency>Agency2</TravelAgency>
        <UniqueObjectiveID>C</UniqueObjectiveID>
        <ObjectiveNumber>4</ObjectiveNumber>
    </Place>
</VisitedPlaces> 

How to count number of rows per group greater than the average of that group in pandas group by?


By : mrfazeli
Date : March 29 2020, 07:55 AM
Any of those help I have a data frame df and I use several columns from it to groupby: , You could also aggregate using a lambda function as the following:
code :
df.groupby(['col1', 'col2']).agg(['mean', 'count', 
                                  lambda x: (x > x.mean()).sum()])

SQL Server : create group of N rows each and give group number for each group


By : user3396534
Date : March 29 2020, 07:55 AM
Does that help Use row_number() to enumerate the rows, arithmetic to assign the group and then take the minimum of the id:
code :
SELECT t.*, MIN(ID) OVER (PARTITION BY grp) as groupnumber       
FROM (SELECT t.*,
            ( (ROW_NUMBER() OVER (ORDER BY ID) - 1) / 3) as grp
      FROM Table
     ) t
ORDER BY ID ASC;
select t.*,
       (case when row_number() over (order by id) % 3 = 0
             then lag(id, 2) over (order by id)
             when row_number() over (order by id) % 3 = 2
             then lag(id, 1) over (order by id)
             else id
        end) as groupnumber
from table t
order by id;
Related Posts Related Posts :
  • SQL Order By: Specific Value First, then Ordering?
  • SQL Developer - SELECT statement with AVG calculation displaying multiple averages?
  • Microsoft SQL Server : return only the rows with the most recent date for each unique ID
  • How to create a queue like structure in SQL Server
  • Cross apply on columns on SQL server. Syntax error near )
  • Select count of subquery results, along with another field
  • SQL Server 2016 - Five Column to single row
  • Insert zero values for unexisting groups in Redshift
  • SQL LIKE '%...' in vba HTTP request
  • SQL Developer - How do I get MAX & AVG to draw from one column and filter from themselves?
  • How to optimize SQL query with multiple selects
  • Postgresql 11 - Create Procedure to Execute COPY function
  • Oracle VIEW - Convert VARCHAR2 to DATE and find MIN/MAX of DATE
  • In SQL, Main table should match with lookup table for the multiple columns
  • Very slow performance for inner join with restrictive where clause (very small subset of rows)
  • oracle sql 2 columns in one column
  • SQL query to get conflicting values in JSONB from a group
  • Two queries with same tables and join logic but different results
  • How to update a table joined with another table multiple times in Oracle?
  • Crystal Report how can I get data for every 5th of months
  • Finding MAX() from a serial number field which contains characters in SQL Server
  • Getting all distinct values and sorting them by hour
  • Database Compatibility Level
  • Combine WITH statement with UNION
  • Self Nested SQL join
  • My store procedure only executes my first select statement while I wrote three?
  • Inserting Date gives error ORA-01861: literal does not match format string
  • Need to join row data into columns in Oracle
  • Syntax error Missing operator in my case statement
  • Filter the values from the source in merge query in mssql
  • How can i replace/convert number to string
  • Updating a table, on a button press, with selected information
  • Oracle SQL report generation without null
  • SQL get the names of the columns that are stored in a different table
  • Number masking & nls_parameter
  • Delete duplicate roles for SQL server
  • SQL Server: How to change collation of all columns of my select on the fly
  • SQL insert into statement leads to weird (cartesian product) result
  • check constraint sql server
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org