问:
Find last column with data and add data after that column
I have some data in my worksheet and what I want to achieve is that I want to add data after 1 column from the last column with data.
I have tried the Range().End property but I think I am not able to implement it correctly.

```<code>colnum = Sheet3.Range("A:Z").End(xlToLeft).Column + 2
</code>```

The above code gives me the column number as '1' and plus 2 means it gives column number '3' which is 'C'.
Data in my worksheet is something like this:

I want a dynamic search and my code should find column 'E' as the last column with data and start adding data from column 'F'. Any suggestions are appreciated.

答:
This function returns an array where the first element is the last row number, and the second element the last column number.

```<code>Function LastRowCol(Worksht) As Long()
'Uncomment if on worksheet
'Application.Volatile
    Dim WS As Worksheet, R As Range
    Dim LastRow As Long, LastCol As Long
    Dim L(1) As Long

Select Case TypeName(Worksht)
    Case "String"
        Set WS = Worksheets(Worksht)
    Case "Worksheet"
        Set WS = Worksht
End Select

With WS
    Set R = .Cells.Find(what:="*", after:=.Cells(1, 1), _
                    LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByRows, _
                    searchdirection:=xlPrevious)

    If Not R Is Nothing Then
        LastRow = R.Row
        LastCol = .Cells.Find(what:="*", after:=.Cells(1, 1), _
                    LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByColumns, _
                    searchdirection:=xlPrevious).Column
    Else
        LastRow = 1
        LastCol = 1
    End If
End With

L(0) = LastRow
L(1) = LastCol
LastRowCol = L
End Function

Sub change_code_name()
'Requires Trust Access to VBA Object Model

  Dim wbk As Object, sheet As Object
ActiveWorkbook.VBProject.name = "VBAProject"
Set wbk = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.CodeName)
  wbk.name = "wbk_code_name"
Set sheet = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.Sheets(1).CodeName)
  sheet.name = "sheet_code_name"
End Sub

</code>```

In the case of your code line, assuming your worksheet CodeName is Sheet3, it would be something like:

```<code>colnum = LastRowCol(Sheet3)(1) + 1
</code>```

and would apply to the active workbook.

See also Error in finding last used cell in Excel with VBA for an extensive discussion of the various methods of finding the last row/column and the pitfalls of each.

Edit:

Examining your screenshot, it seems that you might want to exclude Row 1 from the testing.  If that is the case, then try this slightly modified code:

```<code>Option Explicit
Function LastRowCol(Worksht, Optional excludeRows As Long = 0) As Long()
'Uncomment if on worksheet
'Application.Volatile
    Dim WS As Worksheet, R As Range
    Dim LastRow As Long, LastCol As Long
    Dim L(1) As Long
    Dim searchRng As Range

Select Case TypeName(Worksht)
    Case "String"
        Set WS = Worksheets(Worksht)
    Case "Worksheet"
        Set WS = Worksht
End Select

If excludeRows > 0 Then
    With WS
        Set searchRng = Range(.Cells(excludeRows + 1, 1), .Cells(.Rows.Count, .Columns.Count))
    End With
Else
    Set searchRng = WS.Cells
End If

With searchRng
    Set R = .Cells.Find(what:="*", after:=.Cells(1, 1), _
                    LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByRows, _
                    searchdirection:=xlPrevious)

    If Not R Is Nothing Then
        LastRow = R.Row
        LastCol = .Cells.Find(what:="*", after:=.Cells(1, 1), _
                    LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByColumns, _
                    searchdirection:=xlPrevious).Column
    Else
        LastRow = 1
        LastCol = 1
    End If
End With

L(0) = excludeRows + 1
L(1) = LastCol
LastRowCol = L
End Function
</code>```

and, in your macro:

```<code>colnum = LastRowCol(Sheet3,1)(1) + 1
</code>```

Body:
I have some data in my worksheet and what I want to achieve is that I want to add data after 1 column from the last column with data.
I have tried the Range().End property but I think I am not able to implement it correctly.

colnum = Sheet3.Range("A:Z").End(xlToLeft).Column + 2

The above code gives me the column number as '1' and plus 2 means it gives column number '3' which is 'C'.
Data in my worksheet is something like this:

I want a dynamic search and my code should find column 'E' as the last column with data and start adding data from column 'F'. Any suggestions are appreciated.

Body:
This function returns an array where the first element is the last row number, and the second element the last column number.

Function LastRowCol(Worksht) As Long()
'Uncomment if on worksheet
'Application.Volatile
    Dim WS As Worksheet, R As Range
    Dim LastRow As Long, LastCol As Long
    Dim L(1) As Long

Select Case TypeName(Worksht)
    Case "String"
        Set WS = Worksheets(Worksht)
    Case "Worksheet"
        Set WS = Worksht
End Select

With WS
    Set R = .Cells.Find(what:="*", after:=.Cells(1, 1), _
                    LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByRows, _
                    searchdirection:=xlPrevious)

    If Not R Is Nothing Then
        LastRow = R.Row
        LastCol = .Cells.Find(what:="*", after:=.Cells(1, 1), _
                    LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByColumns, _
                    searchdirection:=xlPrevious).Column
    Else
        LastRow = 1
        LastCol = 1
    End If
End With

L(0) = LastRow
L(1) = LastCol
LastRowCol = L
End Function

Sub change_code_name()
'Requires Trust Access to VBA Object Model

  Dim wbk As Object, sheet As Object
ActiveWorkbook.VBProject.name = "VBAProject"
Set wbk = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.CodeName)
  wbk.name = "wbk_code_name"
Set sheet = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.Sheets(1).CodeName)
  sheet.name = "sheet_code_name"
End Sub

In the case of your code line, assuming your worksheet CodeName is Sheet3, it would be something like:

colnum = LastRowCol(Sheet3)(1) + 1

and would apply to the active workbook.

See also Error in finding last used cell in Excel with VBA for an extensive discussion of the various methods of finding the last row/column and the pitfalls of each.

Edit:

Examining your screenshot, it seems that you might want to exclude Row 1 from the testing.  If that is the case, then try this slightly modified code:

Option Explicit
Function LastRowCol(Worksht, Optional excludeRows As Long = 0) As Long()
'Uncomment if on worksheet
'Application.Volatile
    Dim WS As Worksheet, R As Range
    Dim LastRow As Long, LastCol As Long
    Dim L(1) As Long
    Dim searchRng As Range

Select Case TypeName(Worksht)
    Case "String"
        Set WS = Worksheets(Worksht)
    Case "Worksheet"
        Set WS = Worksht
End Select

If excludeRows > 0 Then
    With WS
        Set searchRng = Range(.Cells(excludeRows + 1, 1), .Cells(.Rows.Count, .Columns.Count))
    End With
Else
    Set searchRng = WS.Cells
End If

With searchRng
    Set R = .Cells.Find(what:="*", after:=.Cells(1, 1), _
                    LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByRows, _
                    searchdirection:=xlPrevious)

    If Not R Is Nothing Then
        LastRow = R.Row
        LastCol = .Cells.Find(what:="*", after:=.Cells(1, 1), _
                    LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByColumns, _
                    searchdirection:=xlPrevious).Column
    Else
        LastRow = 1
        LastCol = 1
    End If
End With

L(0) = excludeRows + 1
L(1) = LastCol
LastRowCol = L
End Function

and, in your macro:

colnum = LastRowCol(Sheet3,1)(1) + 1


来源: stackexchange

问:
inserting and bolding a string in microsoft vba code
Here is a snippet from code that I have created. It is working at the moment, but now I need to format the document. I am trying to make the third line in this code create an output that is bold, but I am stuck on how to do it.

Snippet of code I am trying to edit:

```<code>    ActiveDocument.Content.InsertAfter (MyArray(iRow, 7))
    ActiveDocument.Content.InsertAfter (MyArray(iRow, 8))
    ActiveDocument.Content.InsertAfter "Correct Answer is: " & (MyArray(iRow, 9))
    ActiveDocument.Content.InsertAfter (MyArray(iRow, 10))
    ActiveDocument.Content.InsertAfter (MyArray(iRow, 11))
    ActiveDocument.Content.InsertAfter (MyArray(iRow, 12))
</code>```

Here is the initial set up in the code in case you are wondering how the variables are set up:

```<code>Dim objWord
Dim ObjNewDoc As Document

Dim RowsCount As Integer
Dim ColsCount As Integer

Dim rngFormat As Range

Dim MyArray() As String

Dim iRow As Integer
Dim iCol As Integer
</code>```

I am trying to make the third line's output bold while leaving the remainder not bold. However, I am keep getting errors in VBA and am unsure how to overcome them.

On the Microsoft VBA reference website, it says the syntax is, "expression.Bold". I tried to insert it and it created an error obviously.

答:
Something like this should work. As you enter text, you create a range that captures the line to be bolded. If we bolded it as the text is inserted, the following text would also be bold, so we wit until the end to apply the formatting:

```<code>Sub BoldOnePhrase()
    Dim oRange As Range

    ActiveDocument.Content.InsertAfter (MyArray(iRow, 7))
    ActiveDocument.Content.InsertAfter (MyArray(iRow, 8))

    Set oRange = ActiveDocument.Content
    oRange.Collapse Direction:=wdCollapseEnd
    ActiveDocument.Content.InsertAfter "Correct Answer is: " & (MyArray(iRow, 9))
    oRange.MoveEnd Unit:=wdWord, Count:=5

    ActiveDocument.Content.InsertAfter (MyArray(iRow, 10))
    ActiveDocument.Content.InsertAfter (MyArray(iRow, 11))

    oRange.Font.Bold = True
End Sub
</code>```

If your array holds multi-word answers, you'll have to adjust the number of words to move the end of the range.

ContentLicense: CC BY-SA 4.0

Body:
Here is a snippet from code that I have created. It is working at the moment, but now I need to format the document. I am trying to make the third line in this code create an output that is bold, but I am stuck on how to do it.

Snippet of code I am trying to edit:

    ActiveDocument.Content.InsertAfter (MyArray(iRow, 7))
    ActiveDocument.Content.InsertAfter (MyArray(iRow, 8))
    ActiveDocument.Content.InsertAfter "Correct Answer is: " & (MyArray(iRow, 9))
    ActiveDocument.Content.InsertAfter (MyArray(iRow, 10))
    ActiveDocument.Content.InsertAfter (MyArray(iRow, 11))
    ActiveDocument.Content.InsertAfter (MyArray(iRow, 12))

Here is the initial set up in the code in case you are wondering how the variables are set up:

Dim objWord
Dim ObjNewDoc As Document

Dim RowsCount As Integer
Dim ColsCount As Integer

Dim rngFormat As Range

Dim MyArray() As String

Dim iRow As Integer
Dim iCol As Integer

I am trying to make the third line's output bold while leaving the remainder not bold. However, I am keep getting errors in VBA and am unsure how to overcome them.

On the Microsoft VBA reference website, it says the syntax is, "expression.Bold". I tried to insert it and it created an error obviously.

Body:
Something like this should work. As you enter text, you create a range that captures the line to be bolded. If we bolded it as the text is inserted, the following text would also be bold, so we wit until the end to apply the formatting:

Sub BoldOnePhrase()
    Dim oRange As Range

    ActiveDocument.Content.InsertAfter (MyArray(iRow, 7))
    ActiveDocument.Content.InsertAfter (MyArray(iRow, 8))

    Set oRange = ActiveDocument.Content
    oRange.Collapse Direction:=wdCollapseEnd
    ActiveDocument.Content.InsertAfter "Correct Answer is: " & (MyArray(iRow, 9))
    oRange.MoveEnd Unit:=wdWord, Count:=5

    ActiveDocument.Content.InsertAfter (MyArray(iRow, 10))
    ActiveDocument.Content.InsertAfter (MyArray(iRow, 11))

    oRange.Font.Bold = True
End Sub

If your array holds multi-word answers, you'll have to adjust the number of words to move the end of the range.


问:
Why ingress-nginx controller tcp-services not working?
I'm trying to expose a RabbitMQ ssl port via ingress-nginx tcp-services like so:

```<code>$ cat rabbit-expose-amqps.yaml
apiVersion: v1
kind: ConfigMap
metadata:
  name: tcp-services
  namespace: ingress-nginx
data:
  5671: "dev/rabbitmq-rabbitmq-ha:5671"
</code>```

(the RabbitMQ service already listens on this port) but and any attempt to perform openssl s_client -connect my-external-host:5671 times out, and, of course, any attempt to connect to amqps://my-extrenal-host:5671 using amqplib times out as well. The management UI does work, though, so I know the external IP is correct.

It seems that nginx.conf in my ingress-nginx pod gets updated, but something is strange (I think): it DOES configure a listener on port 5671, but the upstream still says "placeholder" with 0.0.0.1:1234 address. I even recycled the pod, just in case, still the same conf file:

```<code>stream {
    upstream upstream_balancer {
            server 0.0.0.1:1234; # placeholder

            balancer_by_lua_block {
                    tcp_udp_balancer.balance()
            }
    }
    ...
    # TCP services
    server {
        preread_by_lua_block {
            ngx.var.proxy_upstream_name="tcp-dev-rabbitmq-rabbitmq-ha-5671";
        }
        listen                  5671;
        proxy_timeout           600s;
        proxy_pass              upstream_balancer;

    }
}
</code>```

How do I get the tcp-services applied correctly?

答: Found the solution: nginx-ingress was installed using their "mandatory.yaml" file - not via Helm. Looking at nginx-ingress helm chart, it appears that in order to expose the tcp service ports, there are more resources to config. If I use the helm chart instead of the yaml directly, the ports get exposed correctly.</p>

Body:
I'm trying to expose a RabbitMQ ssl port via ingress-nginx tcp-services like so:

$ cat rabbit-expose-amqps.yaml
apiVersion: v1
kind: ConfigMap
metadata:
  name: tcp-services
  namespace: ingress-nginx
data:
  5671: "dev/rabbitmq-rabbitmq-ha:5671"

(the RabbitMQ service already listens on this port) but and any attempt to perform openssl s_client -connect my-external-host:5671 times out, and, of course, any attempt to connect to amqps://my-extrenal-host:5671 using amqplib times out as well. The management UI does work, though, so I know the external IP is correct.

It seems that nginx.conf in my ingress-nginx pod gets updated, but something is strange (I think): it DOES configure a listener on port 5671, but the upstream still says "placeholder" with 0.0.0.1:1234 address. I even recycled the pod, just in case, still the same conf file:

stream {
    upstream upstream_balancer {
            server 0.0.0.1:1234; # placeholder

            balancer_by_lua_block {
                    tcp_udp_balancer.balance()
            }
    }
    ...
    # TCP services
    server {
        preread_by_lua_block {
            ngx.var.proxy_upstream_name="tcp-dev-rabbitmq-rabbitmq-ha-5671";
        }
        listen                  5671;
        proxy_timeout           600s;
        proxy_pass              upstream_balancer;

    }
}

How do I get the tcp-services applied correctly?


问:
{Java} Vaadin 14 - Limit user to 1 (one) active UI
I posted this question java-vaadin-14-detect-user-leave-closes-tab-f5-etc but I think I asked the wrong questions.

I want to limit each signed-in user, to just one (1) active browser tab, or UI for that matter.

Someone told me to use VaadinSession.getCurrent().getUIs(), but this list is incremented by 1 (or 2 if @Push is enabled) every time the user refreshes the page. This means I can't check if this list contains more than one (or 2).

I'm lost on this! With native java applications and a built-in login system, I can EASILY limit each user to just one (1) session. It's harder with a browser. Or maybe it isn't?

答:
You could maybe register a UI init listener which closes all other UIs in the same session whenever a new UI is initialized?

See:

Tutorial: UIInitListener

Javadoc

On the other hand, I would also be curious to understand why you want to prevent users from opening multiple tabs.


问:
HTTPS connection from c# client to Tomcat using wsdl.exe
I have a tomcat webservice and a c# .net client that connects to the service. I created the Connection classes with wsdl.exe and everything is fine. But now i have activated ssl in tomcat to use https. In my c# app i only changed the url to https and i get the following error:

The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.

How do i avoid this message? is this because the cretificate is untrusted? How do i tell my client that this connection is trusted?

答:
Yes, most likely the server's certificate isn't trusted for your client.
You can handle the ServicePointManager.ServerCertificateValidationCallback to allow SSL connections to the invalid certificated servers:

```<code>private static bool ValidateRemoteCertificate(object sender, X509Certificate certificate, X509Chain chain, SslPolicyErrors policyErrors)
{
    return true; // allow connection despite any errors
}
...
ServicePointManager.ServerCertificateValidationCallback += ValidateRemoteCertificate;
</code>```

ContentLicense: CC BY-SA 3.0

Body:
Yes, most likely the server's certificate isn't trusted for your client.
You can handle the ServicePointManager.ServerCertificateValidationCallback to allow SSL connections to the invalid certificated servers:

private static bool ValidateRemoteCertificate(object sender, X509Certificate certificate, X509Chain chain, SslPolicyErrors policyErrors)
{
    return true; // allow connection despite any errors
}
...
ServicePointManager.ServerCertificateValidationCallback += ValidateRemoteCertificate;


问:
Dynamic List in Flutter for Json
I'm working with some complex json in dart, and I have an issue creating objects before I know what type they'll be.

I appreciate the suggestions, but I don't think I completely understand. In the given answer:

```<code>var entity = Model();
  castToEntity(entity, {'test': 10});
</code>```

Don't I need to know that it will be a Model class?
What if I have the below two classes:

```<code>@JsonSerializable(explicitToJson: true, includeIfNull: false)
class Location {
  String id;
  String resourceType;
Location({@required this.id, this.resourceType})
factory Location.fromJson(Map<String, dynamic> json) => _$LocationFromJson(json);
  Map<String, dynamic> toJson() => _$LocationToJson(this);
}
class Reference {
  String reference;
  String resourceType;
Location({@required this.reference, this.resourceType}
factory Reference.fromJson(Map<String, dynamic> json) => _$ReferenceFromJson(json);
  Map<String, dynamic> toJson() => _$ReferenceToJson(this);
}
</code>```

And then I query the server, and I don't know what kind of class it will be. It could be a Location, or a Reference, or if it's a list, it could be multiple of both, and I don't know until I've requested it.

```<code>var myBundle = Bundle.fromJson(json.decode(response.body));
</code>```

Each "myBundle.entry" is another resource. I'd like to be able to use information from that resource to define itself. So I could do something like:

```<code>myBundle.entry.resourceType newResource = new myBundle.entry.resourceType();
</code>```

What I'm doing right now is sending it to a function that has all of the possible options predefined:

```<code>var newResource = ResourceTypes(myBundle.entry[i].resource.resourceType,
                    myBundle.entry[i].resource.toJson());

dynamic ResourceTypes(String resourceType, Map<String, dynamic> json) {
  if (resourceType == 'Location') return (new Location.fromJson(json));
  if (resourceType == 'Reference') return (new Reference.fromJson(json));
}
</code>```

It was said that there's not reflection in dart, so I didn't know any other way to do it.

答:
As far as I know, it's not possible since Dart doesn't have Reflection like c#, the most close that I can imagine, is using an abstract class that enforces your entity to implement fromJson, and, in that method, you read the Map and put values into fields, like the code below:

```<code>abstract class Serializable {
  void fromJson(Map<String,dynamic> data);
}

class Model implements Serializable {

  int test;
  @override
  void fromJson(data) {
    test = data['test'];
  }
}

Serializable castToEntity(Serializable entity, Map<String, dynamic> data) {
  return entity..fromJson(data);
}
</code>```

Now, when you read you database and have the Map, you can call a method generic like:

```<code>var entity = Model();
  castToEntity(entity, {'test': 10});

  print(entity.test);
</code>```

Where entity is an empty model.

Note: Your fields on entity, cannot be final, since fromJson is an instance method and not a factory method.

Title: Dynamic List in Flutter for Json

Body:
I'm working with some complex json in dart, and I have an issue creating objects before I know what type they'll be.

I appreciate the suggestions, but I don't think I completely understand. In the given answer:

var entity = Model();
  castToEntity(entity, {'test': 10});

Don't I need to know that it will be a Model class?
What if I have the below two classes:

@JsonSerializable(explicitToJson: true, includeIfNull: false)
class Location {
  String id;
  String resourceType;
Location({@required this.id, this.resourceType})
factory Location.fromJson(Map<String, dynamic> json) => _$LocationFromJson(json);
  Map<String, dynamic> toJson() => _$LocationToJson(this);
}
class Reference {
  String reference;
  String resourceType;
Location({@required this.reference, this.resourceType}
factory Reference.fromJson(Map<String, dynamic> json) => _$ReferenceFromJson(json);
  Map<String, dynamic> toJson() => _$ReferenceToJson(this);
}

And then I query the server, and I don't know what kind of class it will be. It could be a Location, or a Reference, or if it's a list, it could be multiple of both, and I don't know until I've requested it.

var myBundle = Bundle.fromJson(json.decode(response.body));

Each "myBundle.entry" is another resource. I'd like to be able to use information from that resource to define itself. So I could do something like:

myBundle.entry.resourceType newResource = new myBundle.entry.resourceType();

What I'm doing right now is sending it to a function that has all of the possible options predefined:

var newResource = ResourceTypes(myBundle.entry[i].resource.resourceType,
                    myBundle.entry[i].resource.toJson());

dynamic ResourceTypes(String resourceType, Map<String, dynamic> json) {
  if (resourceType == 'Location') return (new Location.fromJson(json));
  if (resourceType == 'Reference') return (new Reference.fromJson(json));
}

It was said that there's not reflection in dart, so I didn't know any other way to do it.

Body:
As far as I know, it's not possible since Dart doesn't have Reflection like c#, the most close that I can imagine, is using an abstract class that enforces your entity to implement fromJson, and, in that method, you read the Map and put values into fields, like the code below:

abstract class Serializable {
  void fromJson(Map<String,dynamic> data);
}

class Model implements Serializable {

  int test;
  @override
  void fromJson(data) {
    test = data['test'];
  }
}

Serializable castToEntity(Serializable entity, Map<String, dynamic> data) {
  return entity..fromJson(data);
}

Now, when you read you database and have the Map, you can call a method generic like:

var entity = Model();
  castToEntity(entity, {'test': 10});

  print(entity.test);

Where entity is an empty model.

Note: Your fields on entity, cannot be final, since fromJson is an instance method and not a factory method.


问:
Can we create one IAM User that has access to create other IAM users in AWS
In S3, can we create an IAM user and give it rights to create other IAM users?</p>

答:
The policy specified by Maverick in the above answer can create a new user and create access and secret keys for the user. However, it cannot create or attach any policies to the created user. So, I'm adding the required permission to create and attach IAM and inline policies for IAM users.

```<code>{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "iam:AttachUserPolicy",
                "iam:CreateAccessKey",
                "iam:CreatePolicy",
                "iam:CreateUser",
                "iam:PutUserPolicy"
            ],
            "Resource": "*"
        }
    ]
}
</code>```

Refer this for more information about actions related to Identity and Access Management (IAM) in AWS: https://docs.aws.amazon.com/IAM/latest/UserGuide/list_identityandaccessmanagement.html

Body:
The policy specified by Maverick in the above answer can create a new user and create access and secret keys for the user. However, it cannot create or attach any policies to the created user. So, I'm adding the required permission to create and attach IAM and inline policies for IAM users.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "iam:AttachUserPolicy",
                "iam:CreateAccessKey",
                "iam:CreatePolicy",
                "iam:CreateUser",
                "iam:PutUserPolicy"
            ],
            "Resource": "*"
        }
    ]
}

Refer this for more information about actions related to Identity and Access Management (IAM) in AWS: https://docs.aws.amazon.com/IAM/latest/UserGuide/list_identityandaccessmanagement.html


问:
Value keeps overriding my old value in a session array
I have a very simple random number generator. This number generator has a few options: you input a minimum and maximum number, form this number a random number will be generated. I store the maximum-minimum and generated number inside of a session to be used later while playing the game.

When we get redirected to the main page (the guess game) you have the option to guess the random number that is stored in the session from their one forth the game will give you feedback on how close you are etc.

My question boils down to this, an option of the game is to display all your previous guesses I had a basic idea of how I wanted to do this: I will store the current guess I did inside of an array this array will be linked to a session from which I can loop through all of the old guesses.

I have written a couple of functions to achieve this and it only stores the current guess overriding any number already stored in the array e.g. if my current guess is 6 this number will be stored inside the array, if I proceed to guess again for example 4 this number will be stored inside of the array but will override 6.

I hope my code examples will be more clear.

this function stores the old guesses inside of said array

```<code>public function Store_Old_Guesses_In_Session()
{
    if (!isset($_SESSION["show_previous_guesses"])) {
        return false;
    }
    if (isset($_POST["Guess_Button"])) {
        return $_SESSION["old_guesses"] = $this->old_guesses = array(
            "Old_guesses" => $this->getCurrentGuess(),
        );
    }
    return false;
}
</code>```

this function gets the current guess

```<code>public function getCurrentGuess()
{
    if (isset($this->current_guess)) {
        return $this->current_guess;
    }
    return false;
}
</code>```

-

```<code>

namespace Store;

use Get_Number\Get_Numbers;

require_once "Current_Guesses.php";

class Store_Numbers extends Get_Numbers
{
    public array $old_guesses;

    public function __construct()
    {
        parent::__construct();
        if (isset($_SESSION["old_guesses"])) {
            $this->old_guesses = $_SESSION["old_guesses"];
        }
    }

    public function Store_Current_Number_In_Session()
    {
        if (empty($this->getCurrentNumber())) {
            return false;
        }
        return $_SESSION["current_number"] = $this->getCurrentNumber();
    }

    public function Store_Minimum_Number_In_Session()
    {
        if (empty($this->getMinimumNumber())) {
            return false;
        }
        return $_SESSION["minimum_number"] = $this->getMinimumNumber();
    }

    public function Store_Maximum_Number_In_Session()
    {
        if (empty($this->getMaximumNumber())) {
            return false;
        }
        return $_SESSION["maximum_number"] = $this->getMaximumNumber();
    }

    public function Store_Checkbox_Value_In_Session()
    {
        if (empty($_POST["debug_options"])) {
            return false;
        }
        return $_SESSION["debug_options"] = $_POST["debug_options"];
    }

    public function Store_show_previous_guesses_Checkbox_In_Session()
    {
        if (empty($_POST["show_previous_guesses"])) {
            return false;
        }
        return $_SESSION["show_previous_guesses"] = $_POST["show_previous_guesses"];
    }

    public function Store_Current_Number_Checkbox_Value_In_Session()
    {
        if (empty($_POST["current_number_checkbox"])) {
            return false;
        }
        return $_SESSION["current_number_checkbox"] = $_POST["current_number_checkbox"];
    }

    public function Store_Old_Guesses_In_Session()
    {
        if (!isset($_SESSION["show_previous_guesses"])) {
            return false;
        }
        if (isset($_POST["Guess_Button"])) {
            $this->old_guesses[] = $this->getCurrentGuess();
            $_SESSION['old_guesses'] = array_push($this->old_guesses, $this->getCurrentGuess());
            return $this->old_guesses;
        }
        return false;
    }
}
</code>```

I have a bunch of code examples should you need more

A few things you might need to know:

PHP = 7.4.2

The property $old_guesses is declared as an array like this public
array $old_guesses;

答:
You need to push onto the array, not assign to it.

```<code>public function Store_Old_Guesses_In_Session()
{
    if (!isset($_SESSION["show_previous_guesses"])) {
        return false;
    }
    if (isset($_POST["Guess_Button"])) {
        $this->old_guesses[] = $this->getCurrentGuess();
        $_SESSION['old_guesses'] = $this->old_guesses;
        return $this->old_guesses;
    }
    return false;
}
</code>```

The constructor should initialize the variable from the session variable to make it persist and grow.

```<code>if (!isset($_SESSION['old_guesses'])) {
    $_SESSION['old_guesses'] = array();
}

$this->old_guesses = $_SESSION['old_guesses'];
</code>```

Body:
I have a very simple random number generator. This number generator has a few options: you input a minimum and maximum number, form this number a random number will be generated. I store the maximum-minimum and generated number inside of a session to be used later while playing the game.

When we get redirected to the main page (the guess game) you have the option to guess the random number that is stored in the session from their one forth the game will give you feedback on how close you are etc.

My question boils down to this, an option of the game is to display all your previous guesses I had a basic idea of how I wanted to do this: I will store the current guess I did inside of an array this array will be linked to a session from which I can loop through all of the old guesses.

I have written a couple of functions to achieve this and it only stores the current guess overriding any number already stored in the array e.g. if my current guess is 6 this number will be stored inside the array, if I proceed to guess again for example 4 this number will be stored inside of the array but will override 6.

I hope my code examples will be more clear.

this function stores the old guesses inside of said array

public function Store_Old_Guesses_In_Session()
{
    if (!isset($_SESSION["show_previous_guesses"])) {
        return false;
    }
    if (isset($_POST["Guess_Button"])) {
        return $_SESSION["old_guesses"] = $this->old_guesses = array(
            "Old_guesses" => $this->getCurrentGuess(),
        );
    }
    return false;
}

this function gets the current guess

public function getCurrentGuess()
{
    if (isset($this->current_guess)) {
        return $this->current_guess;
    }
    return false;
}

-

namespace Store;

use Get_Number\Get_Numbers;

require_once "Current_Guesses.php";

class Store_Numbers extends Get_Numbers
{
    public array $old_guesses;

    public function __construct()
    {
        parent::__construct();
        if (isset($_SESSION["old_guesses"])) {
            $this->old_guesses = $_SESSION["old_guesses"];
        }
    }

    public function Store_Current_Number_In_Session()
    {
        if (empty($this->getCurrentNumber())) {
            return false;
        }
        return $_SESSION["current_number"] = $this->getCurrentNumber();
    }

    public function Store_Minimum_Number_In_Session()
    {
        if (empty($this->getMinimumNumber())) {
            return false;
        }
        return $_SESSION["minimum_number"] = $this->getMinimumNumber();
    }

    public function Store_Maximum_Number_In_Session()
    {
        if (empty($this->getMaximumNumber())) {
            return false;
        }
        return $_SESSION["maximum_number"] = $this->getMaximumNumber();
    }

    public function Store_Checkbox_Value_In_Session()
    {
        if (empty($_POST["debug_options"])) {
            return false;
        }
        return $_SESSION["debug_options"] = $_POST["debug_options"];
    }

    public function Store_show_previous_guesses_Checkbox_In_Session()
    {
        if (empty($_POST["show_previous_guesses"])) {
            return false;
        }
        return $_SESSION["show_previous_guesses"] = $_POST["show_previous_guesses"];
    }

    public function Store_Current_Number_Checkbox_Value_In_Session()
    {
        if (empty($_POST["current_number_checkbox"])) {
            return false;
        }
        return $_SESSION["current_number_checkbox"] = $_POST["current_number_checkbox"];
    }

    public function Store_Old_Guesses_In_Session()
    {
        if (!isset($_SESSION["show_previous_guesses"])) {
            return false;
        }
        if (isset($_POST["Guess_Button"])) {
            $this->old_guesses[] = $this->getCurrentGuess();
            $_SESSION['old_guesses'] = array_push($this->old_guesses, $this->getCurrentGuess());
            return $this->old_guesses;
        }
        return false;
    }
}

I have a bunch of code examples should you need more

A few things you might need to know:

PHP = 7.4.2

The property $old_guesses is declared as an array like this public
array $old_guesses;

Body:
You need to push onto the array, not assign to it.

public function Store_Old_Guesses_In_Session()
{
    if (!isset($_SESSION["show_previous_guesses"])) {
        return false;
    }
    if (isset($_POST["Guess_Button"])) {
        $this->old_guesses[] = $this->getCurrentGuess();
        $_SESSION['old_guesses'] = $this->old_guesses;
        return $this->old_guesses;
    }
    return false;
}

The constructor should initialize the variable from the session variable to make it persist and grow.

if (!isset($_SESSION['old_guesses'])) {
    $_SESSION['old_guesses'] = array();
}

$this->old_guesses = $_SESSION['old_guesses'];


问:
Optimizing R objective function with Rcpp slower, why?
I am currently working on a Bayesian method that requires multiple steps of optimisation of a multinomial logit model per iteration. I am using optim() to perform those optimisations, and an objective function written in R. A profiling revealed that optim() is the main bottleneck.

After digging around, I found this question in which they suggest that recoding the objective function with Rcpp could speed up the process. I followed the suggestion and recoded my objective function with Rcpp, but it ended up being slower (about two times slower!).

This was my first time with Rcpp (or anything related to C++) and I was not able to find a way of vectorising the code. Any idea how to make it faster?

Tl;dr: Current implementation of function in Rcpp is not as fast as vectorised R; how to make it faster?

A reproducible example:

Define objective functions in R and Rcpp: log-likelihood of an intercept only multinomial model

```<code>library(Rcpp)
library(microbenchmark)

llmnl_int <- function(beta, Obs, n_cat) {
  n_Obs     <- length(Obs)
  Xint      <- matrix(c(0, beta), byrow = T, ncol = n_cat, nrow = n_Obs)
  ind       <- cbind(c(1:n_Obs), Obs)
  Xby       <- Xint[ind]
  Xint      <- exp(Xint)
  iota      <- c(rep(1, (n_cat)))
  denom     <- log(Xint %*% iota)
  return(sum(Xby - denom))
}

cppFunction('double llmnl_int_C(NumericVector beta, NumericVector Obs, int n_cat) {

    int n_Obs = Obs.size();

    NumericVector betas = (beta.size()+1);
    for (int i = 1; i < n_cat; i++) {
        betas[i] = beta[i-1];
    };

    NumericVector Xby = (n_Obs);
    NumericMatrix Xint(n_Obs, n_cat);
    NumericVector denom = (n_Obs);
    for (int i = 0; i < Xby.size(); i++) {
        Xint(i,_) = betas;
        Xby[i] = Xint(i,Obs[i]-1.0);
        Xint(i,_) = exp(Xint(i,_));
        denom[i] = log(sum(Xint(i,_)));
    };

    return sum(Xby - denom);
}')

</code>```

Compare their efficiency:

```<code>## Draw sample from a multinomial distribution
set.seed(2020)
mnl_sample <- t(rmultinom(n = 1000,size = 1,prob = c(0.3, 0.4, 0.2, 0.1)))
mnl_sample <- apply(mnl_sample,1,function(r) which(r == 1))

## Benchmarking
microbenchmark("llmml_int" = llmnl_int(beta = c(4,2,1), Obs = mnl_sample, n_cat = 4),
               "llmml_int_C" = llmnl_int_C(beta = c(4,2,1), Obs = mnl_sample, n_cat = 4),
               times = 100)
## Results
# Unit: microseconds
#         expr     min       lq     mean   median       uq     max neval
#    llmnl_int  76.809  78.6615  81.9677  79.7485  82.8495 124.295   100
#  llmnl_int_C 155.405 157.7790 161.7677 159.2200 161.5805 201.655   100

</code>```

Now calling them in optim:

```<code>## Benchmarking with optim
microbenchmark("llmnl_int" = optim(c(4,2,1), llmnl_int, Obs = mnl_sample, n_cat = 4, method = "BFGS", hessian = T, control = list(fnscale = -1)),
               "llmnl_int_C" = optim(c(4,2,1), llmnl_int_C, Obs = mnl_sample, n_cat = 4, method = "BFGS", hessian = T, control = list(fnscale = -1)),
               times = 100)
## Results
# Unit: milliseconds
#         expr      min       lq     mean   median       uq      max neval
#    llmnl_int 12.49163 13.26338 15.74517 14.12413 18.35461 26.58235   100
#  llmnl_int_C 25.57419 25.97413 28.05984 26.34231 30.44012 37.13442   100

</code>```

I was somewhat surprised that the vectorised implementation in R was faster. Implementing a more efficient version in Rcpp (say, with RcppArmadillo?) can produce any gains? Is it a better idea to recode everything in Rcpp using a C++ optimiser?

答:
Your C++ function can be made faster using the following observations. At least the first might also be used with your R function:

The way you calculate denom[i] is the same for every i. It therefore makes sense to use a double denom and do this calculation only once. I also factor out subtracting this common term in the end.

Your observations are actually an integer vector on the R side, and you are using them as integers in C++ as well. Using an IntegerVector to begin with makes a lot of casting unnecessary.

You can index a NumericVector using an IntegerVector in C++ as well. I am not sure if this helps performance, but it makes the code a bit shorter.

Some more changes which are more related to style than performance.

Result:

double llmnl_int_C(NumericVector beta, IntegerVector Obs, int n_cat) {

    int n_Obs = Obs.size();

    NumericVector betas(beta.size()+1);
    for (int i = 1; i < n_cat; ++i) {
        betas[i] = beta[i-1];
    };

    double denom = log(sum(exp(betas)));
    NumericVector Xby = betas[Obs - 1];

    return sum(Xby) - n_Obs * denom;
}
```

For me this function is roughly ten times faster than your R function.

Body:
I am currently working on a Bayesian method that requires multiple steps of optimisation of a multinomial logit model per iteration. I am using optim() to perform those optimisations, and an objective function written in R. A profiling revealed that optim() is the main bottleneck.

After digging around, I found this question in which they suggest that recoding the objective function with Rcpp could speed up the process. I followed the suggestion and recoded my objective function with Rcpp, but it ended up being slower (about two times slower!).

This was my first time with Rcpp (or anything related to C++) and I was not able to find a way of vectorising the code. Any idea how to make it faster?

Tl;dr: Current implementation of function in Rcpp is not as fast as vectorised R; how to make it faster?

A reproducible example:

Define objective functions in R and Rcpp: log-likelihood of an intercept only multinomial model

library(Rcpp)
library(microbenchmark)

llmnl_int <- function(beta, Obs, n_cat) {
  n_Obs     <- length(Obs)
  Xint      <- matrix(c(0, beta), byrow = T, ncol = n_cat, nrow = n_Obs)
  ind       <- cbind(c(1:n_Obs), Obs)
  Xby       <- Xint[ind]
  Xint      <- exp(Xint)
  iota      <- c(rep(1, (n_cat)))
  denom     <- log(Xint %*% iota)
  return(sum(Xby - denom))
}

cppFunction('double llmnl_int_C(NumericVector beta, NumericVector Obs, int n_cat) {

    int n_Obs = Obs.size();

    NumericVector betas = (beta.size()+1);
    for (int i = 1; i < n_cat; i++) {
        betas[i] = beta[i-1];
    };

    NumericVector Xby = (n_Obs);
    NumericMatrix Xint(n_Obs, n_cat);
    NumericVector denom = (n_Obs);
    for (int i = 0; i < Xby.size(); i++) {
        Xint(i,_) = betas;
        Xby[i] = Xint(i,Obs[i]-1.0);
        Xint(i,_) = exp(Xint(i,_));
        denom[i] = log(sum(Xint(i,_)));
    };

    return sum(Xby - denom);
}')

Compare their efficiency:

## Draw sample from a multinomial distribution
set.seed(2020)
mnl_sample <- t(rmultinom(n = 1000,size = 1,prob = c(0.3, 0.4, 0.2, 0.1)))
mnl_sample <- apply(mnl_sample,1,function(r) which(r == 1))

## Benchmarking
microbenchmark("llmml_int" = llmnl_int(beta = c(4,2,1), Obs = mnl_sample, n_cat = 4),
               "llmml_int_C" = llmnl_int_C(beta = c(4,2,1), Obs = mnl_sample, n_cat = 4),
               times = 100)
## Results
# Unit: microseconds
#         expr     min       lq     mean   median       uq     max neval
#    llmnl_int  76.809  78.6615  81.9677  79.7485  82.8495 124.295   100
#  llmnl_int_C 155.405 157.7790 161.7677 159.2200 161.5805 201.655   100

Now calling them in optim:

## Benchmarking with optim
microbenchmark("llmnl_int" = optim(c(4,2,1), llmnl_int, Obs = mnl_sample, n_cat = 4, method = "BFGS", hessian = T, control = list(fnscale = -1)),
               "llmnl_int_C" = optim(c(4,2,1), llmnl_int_C, Obs = mnl_sample, n_cat = 4, method = "BFGS", hessian = T, control = list(fnscale = -1)),
               times = 100)
## Results
# Unit: milliseconds
#         expr      min       lq     mean   median       uq      max neval
#    llmnl_int 12.49163 13.26338 15.74517 14.12413 18.35461 26.58235   100
#  llmnl_int_C 25.57419 25.97413 28.05984 26.34231 30.44012 37.13442   100

I was somewhat surprised that the vectorised implementation in R was faster. Implementing a more efficient version in Rcpp (say, with RcppArmadillo?) can produce any gains? Is it a better idea to recode everything in Rcpp using a C++ optimiser?

Body:
Your C++ function can be made faster using the following observations. At least the first might also be used with your R function:

The way you calculate denom[i] is the same for every i. It therefore makes sense to use a double denom and do this calculation only once. I also factor out subtracting this common term in the end.

Your observations are actually an integer vector on the R side, and you are using them as integers in C++ as well. Using an IntegerVector to begin with makes a lot of casting unnecessary.

You can index a NumericVector using an IntegerVector in C++ as well. I am not sure if this helps performance, but it makes the code a bit shorter.

Some more changes which are more related to style than performance.

Result:

double llmnl_int_C(NumericVector beta, IntegerVector Obs, int n_cat) {

    int n_Obs = Obs.size();

    NumericVector betas(beta.size()+1);
    for (int i = 1; i < n_cat; ++i) {
        betas[i] = beta[i-1];
    };

    double denom = log(sum(exp(betas)));
    NumericVector Xby = betas[Obs - 1];

    return sum(Xby) - n_Obs * denom;
}

For me this function is roughly ten times faster than your R function.


问:
Trying to lookup a value from a pandas dataframe within a range of two rows in the index dataframe
I have two dataframes - "grower_moo" and "pricing" in a Python notebook to analyze harvested crops and price payments to the growers.

pricing is the index dataframe, and grower_moo has various unique load tickets with information about each load.

I need to pull the price per ton from the pricing index to a new column in the load data if the Fat of that load is not greater than the next Wet Fat.

Below is a .head() sample of each dataframe and the code I tried. I received a ValueError: Can only compare identically-labeled Series objects error.

```<code>
pricing
    Price_Per_Ton   Wet_Fat
0       306            10
1       339            11
2       382            12
3       430            13
4       481            14
5       532            15
6       580            16
7       625            17
8       665            18
9       700            19
10      728            20
11      750            21
12      766            22
13      778            23
14      788            24
15      797            25

grower_moo
    Load Ticket     Net Fruit Weight  Net MOO  Percent_MOO  Fat
0   L2019000011817     56660           833     1.448872    21.92
1   L2019000011816     53680           1409    2.557679    21.12
2   L2019000011815     53560           1001    1.834644    21.36
3   L2019000011161     62320           2737    4.207080    21.41
4   L2019000011160     57940           1129    1.911324    20.06
</code>```

grower_moo['price_per_ton'] = max(pricing[pricing['Wet_Fat'] < grower_moo['Fat']]['Price_Per_Ton'])

Example output - grower_moo['Fat'] of 13.60 is less than 14 Fat, therefore gets a price per ton of $430

```<code>grower_moo_with_price
    Load Ticket     Net Fruit Weight  Net MOO  Percent_MOO  Fat    price_per_ton
0   L2019000011817     56660           833     1.448872    21.92      750
1   L2019000011816     53680           1409    2.557679    21.12      750
2   L2019000011815     53560           1001    1.834644    21.36      750
3   L2019000011161     62320           2737    4.207080    21.41      750
4   L2019000011160     57940           1129    1.911324    20.06      728
</code>```

答:
```<code>concat_df = pd.concat([grower_moo, pricing], axis)
cocnat_df = concat_df[concat_df['Wet_Fat'] < concat_df['Fat']]
del cocnat_df['Wet_Fat']
</code>```

Body:
I have two dataframes - "grower_moo" and "pricing" in a Python notebook to analyze harvested crops and price payments to the growers.

pricing is the index dataframe, and grower_moo has various unique load tickets with information about each load.

I need to pull the price per ton from the pricing index to a new column in the load data if the Fat of that load is not greater than the next Wet Fat.

Below is a .head() sample of each dataframe and the code I tried. I received a ValueError: Can only compare identically-labeled Series objects error.

pricing
    Price_Per_Ton   Wet_Fat
0       306            10
1       339            11
2       382            12
3       430            13
4       481            14
5       532            15
6       580            16
7       625            17
8       665            18
9       700            19
10      728            20
11      750            21
12      766            22
13      778            23
14      788            24
15      797            25

grower_moo
    Load Ticket     Net Fruit Weight  Net MOO  Percent_MOO  Fat
0   L2019000011817     56660           833     1.448872    21.92
1   L2019000011816     53680           1409    2.557679    21.12
2   L2019000011815     53560           1001    1.834644    21.36
3   L2019000011161     62320           2737    4.207080    21.41
4   L2019000011160     57940           1129    1.911324    20.06

grower_moo['price_per_ton'] = max(pricing[pricing['Wet_Fat'] < grower_moo['Fat']]['Price_Per_Ton'])

Example output - grower_moo['Fat'] of 13.60 is less than 14 Fat, therefore gets a price per ton of $430

grower_moo_with_price
    Load Ticket     Net Fruit Weight  Net MOO  Percent_MOO  Fat    price_per_ton
0   L2019000011817     56660           833     1.448872    21.92      750
1   L2019000011816     53680           1409    2.557679    21.12      750
2   L2019000011815     53560           1001    1.834644    21.36      750
3   L2019000011161     62320           2737    4.207080    21.41      750
4   L2019000011160     57940           1129    1.911324    20.06      728


问:
Date Conversion from value in C#
Does anyone possibly recognize the following value "40195.315752" as a date?  I need to convert/format this value-based date to a System.DateTime object, but don't understand it's format.

Thanks.

答: That would possibly be the number of days since a certain date (possibly january 1st 1900), before the decimal point?</p>

Title: Date Conversion from value in C#


问:
Query performance improvements - collecting user_activities in CakePHP 3.0
I'm currently working on a cakephp 3.0 project to create an individual social network. I'm dealing with a problem for my "timeline". There I have a query which first gets a list of all my friends inside this community and then collecting all the user_activities they made. (see query below).

So at the end I have a list with all my friends activities ordered by created... In the FrontEnd I use a foreach to loop through it and have for every "activity.type" a seperate view template (just a text-post, an image-post or a video-post)

For a number of 50-100 Friends its ok... the timeline needs < 1sec. to display properly ... but the community has users with up to 5000 friends (I tested with ~400 friends which had a query runtime of 4-5 seconds).

Do somebody has an idea how I could improve the query or could I do something else to improve the perfomance (only from SQL or PHP side,... I already set up some indexes for certain columns and thought about a limitation by date, e.x. collecting only activities from - 1 year).

I am not very experienced with this topic, so I would appreciate any ideas :)

-------- EDIT

gather friendlist

```<code>SELECT (CASE WHEN `Friends`.`user_id` = '6' THEN `FriendUsers`.`id` ELSE `Friends`.`user_id` END) AS `id` FROM `friends` `Friends` LEFT JOIN `users` `FriendUsers` ON `FriendUsers`.`id` = (`Friends`.`friend_id`) WHERE (`status` >= 0 AND (`Friends`.`user_id` = 6 OR `Friends`.`friend_id` = 6))
</code>```

the query for collecting the user-activities

```<code>SELECT  `Wallposts`.`text` AS `Wallposts__text`, `Activities`.`name` AS `Activities__name`,
        `Users`.`firstname` AS `Users__firstname`, `Users`.`lastname` AS `Users__lastname`,
        `Users`.`username` AS `Users__username`, `Users`.`id` AS `Users__id`,
        `UsersActivities`.`created` AS `UsersActivities__created`,
        `UsersActivities`.`id` AS `UsersActivities__id`, `UsersActivities`.`video_id` AS `UsersActivities__video_id`,
        `UsersActivities`.`picture_id` AS `UsersActivities__picture_id`,
        `UsersActivities`.`visible_for_group` AS `UsersActivities__visible_for_group`,
        `Pictures`.`id` AS `Pictures__id`, `Pictures`.`user_id` AS `Pictures__user_id`,
        `Pictures`.`description` AS `Pictures__description`, `Pictures`.`fileName` AS `Pictures__fileName`,
        `Pictures`.`album_id` AS `Pictures__album_id`, `Pictures`.`isChosen` AS `Pictures__isChosen`,
        `Albums`.`id` AS `Albums__id`, `Albums`.`name` AS `Albums__name`,
        `Videos`.`id` AS `Videos__id`, `Videos`.`hoster` AS `Videos__hoster`,
        `Videos`.`video_key` AS `Videos__video_key`, `Videos`.`name` AS `Videos__name`,
        `Videos`.`description` AS `Videos__description`
    FROM  `users_activities` `UsersActivities`
    left JOIN  `users` `Users`  ON UsersActivities.user_id = Users.id
    left JOIN  `activities` `Activities`  ON UsersActivities.activity_id = Activities.id
    left JOIN  `wallposts` `Wallposts`  ON UsersActivities.wallpost_id = Wallposts.id
    LEFT JOIN  `videos` `Videos`  ON `Videos`.`id` = (`UsersActivities`.`video_id`)
    LEFT JOIN  `pictures` `Pictures`  ON `Pictures`.`id` = (`UsersActivities`.`picture_id`)
    LEFT JOIN  `albums` `Albums`  ON `Albums`.`id` = (`Pictures`.`album_id`)
    WHERE  (`UsersActivities`.`member_id` = 0
              AND  `Users`.`active` not in ('2')
              AND  `UsersActivities`.`user_activity_id` = 0
              AND  (
                    (`UsersActivities`.`user_id` = 390900002
                              AND  `UsersActivities`.`activity_id` not in (5,6)
                    )
                      OR  (`UsersActivities`.`user_id` in
                                (391407850,391511765,
                                        391511432,491511714,391512398,391204138,391407984,391000522,
                                        391408687,391511708,391305910,391511812,391511681,491512107,
                                        391408047,391408494, -- and hundreds more
                                )
                              AND  `UsersActivities`.`visible_for_group` in (0,3)
                          )
                   )
           )
    ORDER BY  `UsersActivities`.`created` desc
</code>```

EXPLAIN JSON FORMAT

```<code>{
  "query_block": {
    "select_id": 1,
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "UsersActivities",
            "access_type": "range",
            "possible_keys": [
              "user_id",
              "activity_id",
              "member_id",
              "user_activity_id",
              "visible_for_group_idx"
            ],
            "key": "user_id",
            "used_key_parts": [
              "user_id"
            ],
            "key_length": "8",
            "rows": 25623,
            "filtered": 100,
            "index_condition": "((`project`.`usersactivities`.`user_id` = 390900002) or (`project`.`usersactivities`.`user_id` in ( *[HUNDREDS OF FRIEND IDS]* )))",
            "attached_condition": "((`project`.`usersactivities`.`user_activity_id` = 0) and (`project`.`usersactivities`.`member_id` = 0) and (((`project`.`usersactivities`.`user_id` = 390900002) and (`project`.`usersactivities`.`activity_id` not in (5,6))) or ((`project`.`usersactivities`.`user_id` in ( *[HUNDRED OF FRIEND IDS]* )) and (`project`.`usersactivities`.`visible_for_group` in (0,3)))))"
          }
        },
        {
          "table": {
            "table_name": "Users",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "id"
            ],
            "key_length": "8",
            "ref": [
              "project.UsersActivities.user_id"
            ],
            "rows": 1,
            "filtered": 100,
            "attached_condition": "(`project`.`users`.`active` <> '2')"
          }
        },
        {
          "table": {
            "table_name": "Activities",
            "access_type": "ALL",
            "possible_keys": [
              "PRIMARY"
            ],
            "rows": 6,
            "filtered": 83.333,
            "using_join_buffer": "Block Nested Loop",
            "attached_condition": "<if>(is_not_null_compl(Activities), (`project`.`activities`.`id` = `project`.`usersactivities`.`activity_id`), true)"
          }
        },
        {
          "table": {
            "table_name": "Wallposts",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "id"
            ],
            "key_length": "4",
            "ref": [
              "project.UsersActivities.wallpost_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "Videos",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "id"
            ],
            "key_length": "4",
            "ref": [
              "project.UsersActivities.video_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "Pictures",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "id"
            ],
            "key_length": "4",
            "ref": [
              "project.UsersActivities.picture_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "Albums",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "id"
            ],
            "key_length": "4",
            "ref": [
              "project.Pictures.album_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        }
      ]
    }
  }
}
</code>```

答:
In the FrontEnd I use a foreach to loop

I stopped reading there.  You should be able to make a single SQL query that locates all your friends, JOIN to their activities, and produces the entire list of activities, sorted by date.

Going back and forth to the databases is costly.  Gathering, digesting, sorting, etc. all the desired data is usually faster when done in the database server and done in a single query.

For more help, we need to see both the "find friends" query and the "gather activities" query so we can help you put them together.

Some notes on the query you provided:

OR prevents the use of indexes, at least for that part.

NOT IN -- ditto.

Don't use LEFT unless you are expecting the 'right' table to be messy.  It makes it harder for us to understand what is going on, and to figure out what optimizations are / are not available.

Will there be a LIMIT applied?  It seems like this might produce a huge amount of data.  Will there be pagination?  Is CakePHP gathering a zillion rows, then parceling out a few?  Bad news.

Suggested index:

```<code>UsersActivities:  (user_activity_id, member_id, created)
</code>```

If you add that index, please provide the EXPLAIN so we can compare it to the current EXPLAIN.

(It looks like most JOINs are on id, which I assume is the PRIMARY KEY of their respective tables.)

Another possible improvement: Currently you have

```<code>SELECT ...
    `Activities`.`name` AS `Activities__name`,
    ...
  left JOIN  `activities` `Activities`
         ON UsersActivities.activity_id = Activities.id
    ...
</code>```

and that seems to be the only reference to Activities.  It seems that it is a 1:many mapping, so perhaps would be an improvement, both in speed and user-friendliness:

```<code>SELECT ...
    ( SELECT GROUP_CONCAT(DISTINCT `name`) FROM activities
        WHERE id = UsersActivities.activity_id
    )  AS `Activities__name`,
    ...
  -- And leave out left JOIN  `activities`
    ...
</code>```

Body:
I'm currently working on a cakephp 3.0 project to create an individual social network. I'm dealing with a problem for my "timeline". There I have a query which first gets a list of all my friends inside this community and then collecting all the user_activities they made. (see query below).

So at the end I have a list with all my friends activities ordered by created... In the FrontEnd I use a foreach to loop through it and have for every "activity.type" a seperate view template (just a text-post, an image-post or a video-post)

For a number of 50-100 Friends its ok... the timeline needs < 1sec. to display properly ... but the community has users with up to 5000 friends (I tested with ~400 friends which had a query runtime of 4-5 seconds).

Do somebody has an idea how I could improve the query or could I do something else to improve the perfomance (only from SQL or PHP side,... I already set up some indexes for certain columns and thought about a limitation by date, e.x. collecting only activities from - 1 year).

I am not very experienced with this topic, so I would appreciate any ideas :)

-------- EDIT

gather friendlist

SELECT (CASE WHEN `Friends`.`user_id` = '6' THEN `FriendUsers`.`id` ELSE `Friends`.`user_id` END) AS `id` FROM `friends` `Friends` LEFT JOIN `users` `FriendUsers` ON `FriendUsers`.`id` = (`Friends`.`friend_id`) WHERE (`status` >= 0 AND (`Friends`.`user_id` = 6 OR `Friends`.`friend_id` = 6))

the query for collecting the user-activities

SELECT  `Wallposts`.`text` AS `Wallposts__text`, `Activities`.`name` AS `Activities__name`,
        `Users`.`firstname` AS `Users__firstname`, `Users`.`lastname` AS `Users__lastname`,
        `Users`.`username` AS `Users__username`, `Users`.`id` AS `Users__id`,
        `UsersActivities`.`created` AS `UsersActivities__created`,
        `UsersActivities`.`id` AS `UsersActivities__id`, `UsersActivities`.`video_id` AS `UsersActivities__video_id`,
        `UsersActivities`.`picture_id` AS `UsersActivities__picture_id`,
        `UsersActivities`.`visible_for_group` AS `UsersActivities__visible_for_group`,
        `Pictures`.`id` AS `Pictures__id`, `Pictures`.`user_id` AS `Pictures__user_id`,
        `Pictures`.`description` AS `Pictures__description`, `Pictures`.`fileName` AS `Pictures__fileName`,
        `Pictures`.`album_id` AS `Pictures__album_id`, `Pictures`.`isChosen` AS `Pictures__isChosen`,
        `Albums`.`id` AS `Albums__id`, `Albums`.`name` AS `Albums__name`,
        `Videos`.`id` AS `Videos__id`, `Videos`.`hoster` AS `Videos__hoster`,
        `Videos`.`video_key` AS `Videos__video_key`, `Videos`.`name` AS `Videos__name`,
        `Videos`.`description` AS `Videos__description`
    FROM  `users_activities` `UsersActivities`
    left JOIN  `users` `Users`  ON UsersActivities.user_id = Users.id
    left JOIN  `activities` `Activities`  ON UsersActivities.activity_id = Activities.id
    left JOIN  `wallposts` `Wallposts`  ON UsersActivities.wallpost_id = Wallposts.id
    LEFT JOIN  `videos` `Videos`  ON `Videos`.`id` = (`UsersActivities`.`video_id`)
    LEFT JOIN  `pictures` `Pictures`  ON `Pictures`.`id` = (`UsersActivities`.`picture_id`)
    LEFT JOIN  `albums` `Albums`  ON `Albums`.`id` = (`Pictures`.`album_id`)
    WHERE  (`UsersActivities`.`member_id` = 0
              AND  `Users`.`active` not in ('2')
              AND  `UsersActivities`.`user_activity_id` = 0
              AND  (
                    (`UsersActivities`.`user_id` = 390900002
                              AND  `UsersActivities`.`activity_id` not in (5,6)
                    )
                      OR  (`UsersActivities`.`user_id` in
                                (391407850,391511765,
                                        391511432,491511714,391512398,391204138,391407984,391000522,
                                        391408687,391511708,391305910,391511812,391511681,491512107,
                                        391408047,391408494, -- and hundreds more
                                )
                              AND  `UsersActivities`.`visible_for_group` in (0,3)
                          )
                   )
           )
    ORDER BY  `UsersActivities`.`created` desc

EXPLAIN JSON FORMAT

{
  "query_block": {
    "select_id": 1,
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "UsersActivities",
            "access_type": "range",
            "possible_keys": [
              "user_id",
              "activity_id",
              "member_id",
              "user_activity_id",
              "visible_for_group_idx"
            ],
            "key": "user_id",
            "used_key_parts": [
              "user_id"
            ],
            "key_length": "8",
            "rows": 25623,
            "filtered": 100,
            "index_condition": "((`project`.`usersactivities`.`user_id` = 390900002) or (`project`.`usersactivities`.`user_id` in ( *[HUNDREDS OF FRIEND IDS]* )))",
            "attached_condition": "((`project`.`usersactivities`.`user_activity_id` = 0) and (`project`.`usersactivities`.`member_id` = 0) and (((`project`.`usersactivities`.`user_id` = 390900002) and (`project`.`usersactivities`.`activity_id` not in (5,6))) or ((`project`.`usersactivities`.`user_id` in ( *[HUNDRED OF FRIEND IDS]* )) and (`project`.`usersactivities`.`visible_for_group` in (0,3)))))"
          }
        },
        {
          "table": {
            "table_name": "Users",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "id"
            ],
            "key_length": "8",
            "ref": [
              "project.UsersActivities.user_id"
            ],
            "rows": 1,
            "filtered": 100,
            "attached_condition": "(`project`.`users`.`active` <> '2')"
          }
        },
        {
          "table": {
            "table_name": "Activities",
            "access_type": "ALL",
            "possible_keys": [
              "PRIMARY"
            ],
            "rows": 6,
            "filtered": 83.333,
            "using_join_buffer": "Block Nested Loop",
            "attached_condition": "<if>(is_not_null_compl(Activities), (`project`.`activities`.`id` = `project`.`usersactivities`.`activity_id`), true)"
          }
        },
        {
          "table": {
            "table_name": "Wallposts",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "id"
            ],
            "key_length": "4",
            "ref": [
              "project.UsersActivities.wallpost_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "Videos",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "id"
            ],
            "key_length": "4",
            "ref": [
              "project.UsersActivities.video_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "Pictures",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "id"
            ],
            "key_length": "4",
            "ref": [
              "project.UsersActivities.picture_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "Albums",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "id"
            ],
            "key_length": "4",
            "ref": [
              "project.Pictures.album_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        }
      ]
    }
  }
}

Body:
In the FrontEnd I use a foreach to loop

I stopped reading there.  You should be able to make a single SQL query that locates all your friends, JOIN to their activities, and produces the entire list of activities, sorted by date.

Going back and forth to the databases is costly.  Gathering, digesting, sorting, etc. all the desired data is usually faster when done in the database server and done in a single query.

For more help, we need to see both the "find friends" query and the "gather activities" query so we can help you put them together.

Some notes on the query you provided:

OR prevents the use of indexes, at least for that part.

NOT IN -- ditto.

Don't use LEFT unless you are expecting the 'right' table to be messy.  It makes it harder for us to understand what is going on, and to figure out what optimizations are / are not available.

Will there be a LIMIT applied?  It seems like this might produce a huge amount of data.  Will there be pagination?  Is CakePHP gathering a zillion rows, then parceling out a few?  Bad news.

Suggested index:

UsersActivities:  (user_activity_id, member_id, created)

If you add that index, please provide the EXPLAIN so we can compare it to the current EXPLAIN.

(It looks like most JOINs are on id, which I assume is the PRIMARY KEY of their respective tables.)

Another possible improvement: Currently you have

SELECT ...
    `Activities`.`name` AS `Activities__name`,
    ...
  left JOIN  `activities` `Activities`
         ON UsersActivities.activity_id = Activities.id
    ...

and that seems to be the only reference to Activities.  It seems that it is a 1:many mapping, so perhaps would be an improvement, both in speed and user-friendliness:

SELECT ...
    ( SELECT GROUP_CONCAT(DISTINCT `name`) FROM activities
        WHERE id = UsersActivities.activity_id
    )  AS `Activities__name`,
    ...
  -- And leave out left JOIN  `activities`
    ...


问:
number of kafka topic partitons and number of different keys in data
I want to use key/value pattern writing to Kafka in order to keep the same order of data writing while reading it.
My question is should the number of partitions in the topic be equal to the number of different keys in the incoming data.
I already know that with the key/value pattern data having the same key will go to the same partition. </p>

<p>Hence if number of partitions is not equal to the number of different keys in data, we can have data having different keys in the same partition?  In this case how data order is kept? </p>

答:
From Kafka docs:

Each partition is an ordered, immutable sequence of records that is
  continually appended to a structured commit log. The records in the
  partitions are each assigned a sequential id number called the offset
  that uniquely identifies each record within the partition.

Messages sent by a producer to a particular topic partition will be
  appended in the order they are sent. That is, if a record M1 is sent
  by the same producer as a record M2, and M1 is sent first, then M1
  will have a lower offset than M2 and appear earlier in the log.

A
  consumer instance sees records in the order they are stored in the
  log.

These are basic rules about Kafka and sending messages with different keys to same partition will not change this. You can even send all messages to same partition still the first message will be appended to the log before subsequent ones and will have lower offset value. Therefore order will be preserved.

Body:
I want to use key/value pattern writing to Kafka in order to keep the same order of data writing while reading it.
My question is should the number of partitions in the topic be equal to the number of different keys in the incoming data.
I already know that with the key/value pattern data having the same key will go to the same partition.

Hence if number of partitions is not equal to the number of different keys in data, we can have data having different keys in the same partition?  In this case how data order is kept?


问:
Matching just the first and second block of an URL
I'm trying to do a regex to match just the second part of a URL and leave the rest behind

For example

https://example.com/first-part/second-part/third-part/?prop=2

result = https://example.com/alt/second-part/

How can I do this?

I'm able to match the first two parts but for when I use the "/" for match it picks the last / one, instead the one before.

I can go the simple way like this:

```<code>RewriteRule ^(.*)first-part\/(.+)\/(.*)\/(.*)$ https://example.com/alt/$2 [R=301,L]
</code>```

The problem is that if the URL is like this:

https://example.com/first-part/second-part/

Result expected. https://example.com/alt/second-part/
It won't even match it

So I'm looking for a more generic alternative, that may match multiple scenarios giving the same result ultimately in the same format:

https://example.com/alt/second-part/

Just knowing how the first-part exactly is and not knowing how anything beyond the second-part will be formated.

答:
Taking into account the recommendations of @Eraklon to avoid the greedy checks I've found out a solution:

```<code>RewriteRule ^first-part\/([^\/]+(\/)?)(.*) https://example.com/alt/$1 [R=301,L]
</code>```

Can be checked here:
https://htaccess.madewithlove.be?share=8973fe68-f137-59a5-b27b-0cbbe3d842bc

It exactly matches the first-part with ^first-part/ and then in enters the group:

```<code>([^\/]+(\/)?)
</code>```

That checks for 1 or more chars that are not a slash /. When it finds the first slash it can be the next section of the URL or the end of the URL.

Not sure if this is the best but the idea is that it matches just 1 pattern for $1 that includes both the end slash and not-slash for the second-part block of the URL.

I've not been able to remove the last bit from the url (the parameters ?parameter=a)

So the result with this form a URL like:

https://example.com/first-part/second-part/third-part/?parameter=a

Will be

https://example.com/alt/second-part/?parameter=a

Fortunately, the parameters are not too bad, but I would have preferred the full solution.

Body:
I'm trying to do a regex to match just the second part of a URL and leave the rest behind

For example

https://example.com/first-part/second-part/third-part/?prop=2

result = https://example.com/alt/second-part/

How can I do this?

I'm able to match the first two parts but for when I use the "/" for match it picks the last / one, instead the one before.

I can go the simple way like this:

RewriteRule ^(.*)first-part\/(.+)\/(.*)\/(.*)$ https://example.com/alt/$2 [R=301,L]

The problem is that if the URL is like this:

https://example.com/first-part/second-part/

Result expected. https://example.com/alt/second-part/
It won't even match it

So I'm looking for a more generic alternative, that may match multiple scenarios giving the same result ultimately in the same format:

https://example.com/alt/second-part/

Just knowing how the first-part exactly is and not knowing how anything beyond the second-part will be formated.

Body:
Taking into account the recommendations of @Eraklon to avoid the greedy checks I've found out a solution:

RewriteRule ^first-part\/([^\/]+(\/)?)(.*) https://example.com/alt/$1 [R=301,L]

Can be checked here:
https://htaccess.madewithlove.be?share=8973fe68-f137-59a5-b27b-0cbbe3d842bc

It exactly matches the first-part with ^first-part/ and then in enters the group:

([^\/]+(\/)?)

That checks for 1 or more chars that are not a slash /. When it finds the first slash it can be the next section of the URL or the end of the URL.

Not sure if this is the best but the idea is that it matches just 1 pattern for $1 that includes both the end slash and not-slash for the second-part block of the URL.

I've not been able to remove the last bit from the url (the parameters ?parameter=a)

So the result with this form a URL like:

https://example.com/first-part/second-part/third-part/?parameter=a

Will be

https://example.com/alt/second-part/?parameter=a

Fortunately, the parameters are not too bad, but I would have preferred the full solution.


问:
How to pad a number with leading zero in a SAS Macro loop counter?
So I have a range of datasets in a specific library. These datasets are named in the format DATASET_YYYYMM, with one dataset for each month. I am trying to append a range of these datasets based on user input for the date range. i.e. If start_date is 01NOV2019 and the end_date is 31JAN2020, I want to append the three datasets: LIBRARY.DATASET_201911, LIBRARY.DATASET_201912 and LIBRARY.DATASET_202001.

The range is obviously variable, so I can't simply name the datasets manually in a set function. Since I need to loop through the years and months in the date range, I believe a macro is the best way to do this. I'm using a loop within the SET statement to append all the datasets. I have copied my example code below. It does work in theory. But in practice, only if we are looping over the months of November and December. As the format of the dataset name has a two digit month, for Jan-Sept it will be 01-09. The month function returns 1-9 however, and of course a 'File DATASET_NAME does not exist' error is thrown.
Problem is I cannot figure out a way to get it to interpret the month with leading 0, without ruining functionality of another part of the loop/macro.

I have tried numerous approaches to format the number as z2, cannot get any to work.
i.e. Including PUTN functions in the DO line for quote_month as follows, it ignores the leading zero when generating the dataset name in the line below.

```<code>%DO quote_month = %SYSFUNC(IFN(&quote_year. = &start_year.,%SYSFUNC(PUTN(&start_month.,z2.)),1,.)) %TO %SYSFUNC(IFN(&quote_year. = &end_year.,%SYSFUNC(PUTN(&end_month.,z2.)),12,.));
</code>```

Below is example code (without any attempt to reformat it to z2) - it will throw an error because it cannot find 'dataset_20201' because it is actually called 'dataset_202001'. The dataset called dataset_combined_example produces the desired output of the code by manually referencing the dataset names which it will be unable to do in practice. Does anyone know how to go about this?

```<code>DATA _NULL_;
    FORMAT start_date end_date DATE9.;
    start_date = '01NOV2019'd;
    end_date = '31JAN2020'd;
    CALL symput('start_date',start_date);
    CALL symput('end_date',end_date);
RUN;

DATA dataset_201911;
   input name $;
   datalines;
Nov1
Nov2
;
RUN;
DATA dataset_201912;
   input name $;
   datalines;
Dec1
Dec2
;
RUN;
DATA dataset_202001;
   input name $;
   datalines;
Jan1
Jan2
;
RUN;

DATA dataset_combined_example;
    SET dataset_201911 dataset_201912 dataset_202001;
RUN;

%MACRO get_table(start_date, end_date);
    %LET start_year = %SYSFUNC(year(&start_date.));
    %LET end_year = %SYSFUNC(year(&end_date.));
    %LET start_month = %SYSFUNC(month(&start_date.));
    %LET end_month = %SYSFUNC(month(&end_date.));
    DATA dataset_combined;
        SET
            %DO quote_year = &start_year. %TO &end_year.;
                %DO quote_month = %SYSFUNC(IFN(&quote_year. = &start_year.,&start_month.,1,.)) %TO %SYSFUNC(IFN(&quote_year. = &end_year.,&end_month.,12,.));
                    dataset_&quote_year.&quote_month.
                %END;
            %END;
        ;
    RUN;
%MEND;
%get_table(&start_date.,&end_date.);
</code>```

答:
You can use the Z format to generate strings with leading zeros.

But your problem is much easier if you use SAS date functions and formats to generate the YYYYMM strings.  Just use a normal iterative %DO loop to cycle the month offset from zero to the number of months between the two dates.

```<code>%macro get_table(start_date, end_date);
%local offset dsname ;
data dataset_combined;
  set
%do offset=0 %to %sysfunc(intck(month,&start_date,&end_date));
  %let dsname=dataset_%sysfunc(intnx(month,&start_date,&offset),yymmn6);
  &dsname.
%end;
  ;
run;
%mend get_table;
</code>```

Result:

```<code>445   options mprint;
446   %get_table(start_date='01NOV2019'd,end_date='31JAN2020'd);
MPRINT(GET_TABLE):   data dataset_combined;
MPRINT(GET_TABLE):   set dataset_201911 dataset_201912 dataset_202001 ;
MPRINT(GET_TABLE):   run;
</code>```

Body:
So I have a range of datasets in a specific library. These datasets are named in the format DATASET_YYYYMM, with one dataset for each month. I am trying to append a range of these datasets based on user input for the date range. i.e. If start_date is 01NOV2019 and the end_date is 31JAN2020, I want to append the three datasets: LIBRARY.DATASET_201911, LIBRARY.DATASET_201912 and LIBRARY.DATASET_202001.

The range is obviously variable, so I can't simply name the datasets manually in a set function. Since I need to loop through the years and months in the date range, I believe a macro is the best way to do this. I'm using a loop within the SET statement to append all the datasets. I have copied my example code below. It does work in theory. But in practice, only if we are looping over the months of November and December. As the format of the dataset name has a two digit month, for Jan-Sept it will be 01-09. The month function returns 1-9 however, and of course a 'File DATASET_NAME does not exist' error is thrown.
Problem is I cannot figure out a way to get it to interpret the month with leading 0, without ruining functionality of another part of the loop/macro.

I have tried numerous approaches to format the number as z2, cannot get any to work.
i.e. Including PUTN functions in the DO line for quote_month as follows, it ignores the leading zero when generating the dataset name in the line below.

%DO quote_month = %SYSFUNC(IFN(&quote_year. = &start_year.,%SYSFUNC(PUTN(&start_month.,z2.)),1,.)) %TO %SYSFUNC(IFN(&quote_year. = &end_year.,%SYSFUNC(PUTN(&end_month.,z2.)),12,.));

Below is example code (without any attempt to reformat it to z2) - it will throw an error because it cannot find 'dataset_20201' because it is actually called 'dataset_202001'. The dataset called dataset_combined_example produces the desired output of the code by manually referencing the dataset names which it will be unable to do in practice. Does anyone know how to go about this?

DATA _NULL_;
    FORMAT start_date end_date DATE9.;
    start_date = '01NOV2019'd;
    end_date = '31JAN2020'd;
    CALL symput('start_date',start_date);
    CALL symput('end_date',end_date);
RUN;

DATA dataset_201911;
   input name $;
   datalines;
Nov1
Nov2
;
RUN;
DATA dataset_201912;
   input name $;
   datalines;
Dec1
Dec2
;
RUN;
DATA dataset_202001;
   input name $;
   datalines;
Jan1
Jan2
;
RUN;

DATA dataset_combined_example;
    SET dataset_201911 dataset_201912 dataset_202001;
RUN;

%MACRO get_table(start_date, end_date);
    %LET start_year = %SYSFUNC(year(&start_date.));
    %LET end_year = %SYSFUNC(year(&end_date.));
    %LET start_month = %SYSFUNC(month(&start_date.));
    %LET end_month = %SYSFUNC(month(&end_date.));
    DATA dataset_combined;
        SET
            %DO quote_year = &start_year. %TO &end_year.;
                %DO quote_month = %SYSFUNC(IFN(&quote_year. = &start_year.,&start_month.,1,.)) %TO %SYSFUNC(IFN(&quote_year. = &end_year.,&end_month.,12,.));
                    dataset_&quote_year.&quote_month.
                %END;
            %END;
        ;
    RUN;
%MEND;
%get_table(&start_date.,&end_date.);

Body:
You can use the Z format to generate strings with leading zeros.

But your problem is much easier if you use SAS date functions and formats to generate the YYYYMM strings.  Just use a normal iterative %DO loop to cycle the month offset from zero to the number of months between the two dates.

%macro get_table(start_date, end_date);
%local offset dsname ;
data dataset_combined;
  set
%do offset=0 %to %sysfunc(intck(month,&start_date,&end_date));
  %let dsname=dataset_%sysfunc(intnx(month,&start_date,&offset),yymmn6);
  &dsname.
%end;
  ;
run;
%mend get_table;

Result:

445   options mprint;
446   %get_table(start_date='01NOV2019'd,end_date='31JAN2020'd);
MPRINT(GET_TABLE):   data dataset_combined;
MPRINT(GET_TABLE):   set dataset_201911 dataset_201912 dataset_202001 ;
MPRINT(GET_TABLE):   run;


问:
Jquery UI Touch Punch click event is not working in Android app
I have used "Jqueryui-touch-punch" library for dragging and dropping. Dragging, dropping functionality and events is working fine in Chrome browser and on IOS Mobile App.

The problems is that on Android app dragging and dropping is working fine but click events is not working in Android App.

Basically what is happening in the code dragging, dropping and click events on id="clickinEventFired" is working fine on chrome browser and ios mobile apps.

But the same code in Android App, Dragging and dropping is working fine but click event is not working.
Always fired drag event when we click on "Drag me around".

Please help me out what mistakes did by me.

code :

$(function() {
  $("#draggable").draggable({
    cursor: 'move',
    start: startDrag,
    //stop: stopDrag}
  });

  $("#clickinEventFired").click(function() {
    alert("Handler for .click() called.");
  });
});

function startDrag() {
  alert('Dragging event is fired');
}```
<pre class="snippet-code-html lang-html prettyprint-override"><code><script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.11.4/jquery-ui.min.js"></script>
<script src="http://code.jquery.com/mobile/1.4.5/jquery.mobile-1.4.5.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jqueryui-touch-punch/0.2.3/jquery.ui.touch-punch.min.js"></script>

<div id="draggable" class="ui-widget-content">
  <p id="clickinEventFired">Drag me around</p>
</div></code>```

Thank you.

答:
With Mobile devices, you want to be more specific.

Here is an example for a draggable and clickable item.

Desktop Test: https://jsfiddle.net/Twisty/sd64ux8p/24/

Mobile Test: https://jsfiddle.net/Twisty/sd64ux8p/24/show/

HTML

```<code><div id="draggable" class="ui-widget">
  <div class="ui-drag-handle"> </div>
  <p id="clickinEventFired" class="ui-widget-content">Drag me around</p>
</div>

<div id="log">
</div>
</code>```

CSS

```<code>#log {
  font-family: monospace;
  font-size: 0.75em;
  height: 6em;
  position: absolute;
  bottom: 0;
  overflow: hidden;
}

#log p {
  padding: 0;
  margin: 0;
}

.ui-widget {
  padding: 0;
  border: 1px solid #ccc;
  border-radius: 6px;
  background: #ccc;
  width: 150px;
}

.ui-drag-handle {
  width: 100%;
  height: 18px;
}

.ui-widget-content {
  background: #fff;
  margin: 0;
  padding: 2px;
  border-radius: 3px;
}
</code>```

JavaScript

```<code>$(function() {
  function log(str) {
    $("#log").prepend("<p>" + str + "</p>");
  }
  $("#draggable").draggable({
    cursor: 'move',
    handle: ".ui-drag-handle",
    start: function() {
      log("Drag Start");
    },
    stop: function() {
      log("Drag Stop");
    }
  });

  $("#clickinEventFired").click(function() {
    log("Handler for .click() called.");
  });
});
</code>```

As we have added a handle, we now create a visual queue where to click to drag. We also separate the event targets so now a "click" cannot be confused for a "drag" event.

Body:
I have used "Jqueryui-touch-punch" library for dragging and dropping. Dragging, dropping functionality and events is working fine in Chrome browser and on IOS Mobile App.

The problems is that on Android app dragging and dropping is working fine but click events is not working in Android App.

Basically what is happening in the code dragging, dropping and click events on id="clickinEventFired" is working fine on chrome browser and ios mobile apps.

But the same code in Android App, Dragging and dropping is working fine but click event is not working.
Always fired drag event when we click on "Drag me around".

Please help me out what mistakes did by me.

code :

$(function() {
  $("#draggable").draggable({
    cursor: 'move',
    start: startDrag,
    //stop: stopDrag}
  });

  $("#clickinEventFired").click(function() {
    alert("Handler for .click() called.");
  });
});

function startDrag() {
  alert('Dragging event is fired');
}

Drag me around

Thank you.

Body:
With Mobile devices, you want to be more specific.

Here is an example for a draggable and clickable item.

Desktop Test: https://jsfiddle.net/Twisty/sd64ux8p/24/

Mobile Test: https://jsfiddle.net/Twisty/sd64ux8p/24/show/

HTML

<div id="draggable" class="ui-widget">
  <div class="ui-drag-handle">&nbsp;</div>
  <p id="clickinEventFired" class="ui-widget-content">Drag me around</p>
</div>

<div id="log">
</div>

CSS

#log {
  font-family: monospace;
  font-size: 0.75em;
  height: 6em;
  position: absolute;
  bottom: 0;
  overflow: hidden;
}

#log p {
  padding: 0;
  margin: 0;
}

.ui-widget {
  padding: 0;
  border: 1px solid #ccc;
  border-radius: 6px;
  background: #ccc;
  width: 150px;
}

.ui-drag-handle {
  width: 100%;
  height: 18px;
}

.ui-widget-content {
  background: #fff;
  margin: 0;
  padding: 2px;
  border-radius: 3px;
}

JavaScript

$(function() {
  function log(str) {
    $("#log").prepend("<p>" + str + "</p>");
  }
  $("#draggable").draggable({
    cursor: 'move',
    handle: ".ui-drag-handle",
    start: function() {
      log("Drag Start");
    },
    stop: function() {
      log("Drag Stop");
    }
  });

  $("#clickinEventFired").click(function() {
    log("Handler for .click() called.");
  });
});

As we have added a handle, we now create a visual queue where to click to drag. We also separate the event targets so now a "click" cannot be confused for a "drag" event.


问:
Mixing Terraform and Serverless Framework
It's more of an open question and I'm just hoping for any opinions and suggestions. I have AWS in mind but it probably can relate also to other cloud providers.

I'd like to provision IaaC solution that will be easily maintainable and cover all the requirements of modern serverless architecture. Terraform is a great tool for defining the infrastructure, has many official resources and stable support from the community. I really like its syntax and the whole concept of modules. However, it's quite bad for working with Lambdas. It also raises another question: should code change be deployed using the same flow as infrastructure change? Where to draw the line between code and infrastructure?

On the other hand, Serverless Framework allows for super easy development and deployment of Lambdas. It's strongly opinionated when it comes to the usage of resources but it comes with some many out-of-the-box features that it's worth it. It shouldn't really be used for defining the whole infrastructure.

My current approach is to define any shared resources using Terraform and any domain-related resources using Serverless. Here I have another issue that is related to my previous questions: deployment dependency. The simple scenario: Lambda.1 adds users to Cognito (shared resource) which has Lambda.2 as a trigger. I have to create a custom solution for managing the deployment order (Lambda.2 has to be deployed first, etc.). It's possible to hook up the Serverless Framework deployment into Terraform but then again: should the code deployment be mixed with infrastructure deployment?

答:
It is totally possible to mix the two and I have had to do so a few times. How this looks actually ends up being simpler than it seems.

First off, if you think about whatever you do with the Serverless Framework as developing microservices (without the associated infrastructure management burden), that takes it one step in the right direction. Then, what you can do is decide that everything that is required to make that microservice work internally is defined within that microservice as a part of the services configuration in the serverless.yml, whether that be DynamoDB tables, Auth0 integrations, Kinesis streams, SQS, SNS, IAM permissions allocated to functions, etc. Keep that all defined as a part of that microservice. Terraform not required.

Now think about what that and other microservices might need to interact with more broadly. They aren't critical for that services internal operation but are critical for integration into the rest of the organisations infrastructure. This includes things like deployment IAM roles used by the Serverless Framework services to deploy into CloudFormation, Relational Databases that have to be shared amongst multiple services and resources, networking elements (VPC's, Security Groups, etc), monolithic clusters like ElasticSearch and Redis ... all of these elements are great candidates for definition outside of the Serverless Framework and work really well with Terraform.

Any resource would be able to connect to these Terraform defined resource as needed, unlike that hard association such as Lambda functions triggered off of an API Gateway endpoint.

Hope that helps


问:
How to check if string contains chars which are not in a list?
I have a question. How do I check if a python string contains chars which are not in a given list?

Here is the list (set):

set("ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789-._")
```

答:
use any to check in SET for each character of string

SET = set("ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789-._")
s = "123#"
print(any(x not in SET for x in s))
```


问:
Is there a way to prevent a query from setting the serial primary key?
I've got a bunch of tables with the 'serial' keyword on a primary key so that auto-increment will work. The problem is that I can make a query to insert a row using any id number which overrides the auto-increment. Is there a reason for this? Or, is there a way to prevent a user from adding/changing this value?

Here's an example of my table config:

```<code>create table if not exists departments (
    department_id serial primary key,
    name varchar(64) not null unique
);
</code>```

if I run the following query, I can add any number to primary key:

```<code>insert into departments (department_id, name) values (9001, 'FooBar')
</code>```

I think I want to prevent this from happening. I'd like to get some opinions.

答:
Use an identity column:

```<code>create table if not exists departments (
    department_id integer primary key generated always as identity,
    name varchar(64) not null unique
);
</code>```

This will prevent an insert to override the generated value. You can still circumvent that by specifying OVERRIDING SYSTEM VALUE as part of your INSERT statement. But unless you specify that option, providing a value for the column will result in an error.

Related: PostgreSQL: serial vs identity

Body:
I've got a bunch of tables with the 'serial' keyword on a primary key so that auto-increment will work. The problem is that I can make a query to insert a row using any id number which overrides the auto-increment. Is there a reason for this? Or, is there a way to prevent a user from adding/changing this value?

Here's an example of my table config:

create table if not exists departments (
    department_id serial primary key,
    name varchar(64) not null unique
);

if I run the following query, I can add any number to primary key:

insert into departments (department_id, name) values (9001, 'FooBar')

I think I want to prevent this from happening. I'd like to get some opinions.

Body:
Use an identity column:

create table if not exists departments (
    department_id integer primary key generated always as identity,
    name varchar(64) not null unique
);

This will prevent an insert to override the generated value. You can still circumvent that by specifying OVERRIDING SYSTEM VALUE as part of your INSERT statement. But unless you specify that option, providing a value for the column will result in an error.

Related: PostgreSQL: serial vs identity


问:
If the sum of the whole column is zero, then change the value of each row in SQL Server
I have a column named Percentage from a temporary table info, which if the sum of the whole column is zero, I want to change the value of each row to 0.5, and if the statement is false just leave it that way.

I have tried to do the following:

```<code>select case when Percentage = 0 then replace(Percentage, 0,0.5) end from ##info
</code>```

I know the above will change the fields whenever they are 0 regardless of the sum of the whole column, and if I do:

```<code>select case when sum(Percentage) = 0 then replace(Percentage, 0,0.5) end from ##info
</code>```

it would throw an error.

Another thing is that I want to replace the temporary table with that column and the above code would only give me the actual column without replacing the original column.

Any suggestions are appreciated

答:
Please find below answer.</p>

```<code>declare @v_val numeric(12,2), @v_new_val numeric(12,2)

set @v_val = 0.00
set @v_new_val = 100

select case when @v_val ='0.00' then @v_new_val else @v_val end
</code>```

Body:
I have a column named Percentage from a temporary table info, which if the sum of the whole column is zero, I want to change the value of each row to 0.5, and if the statement is false just leave it that way.

I have tried to do the following:

select case when Percentage = 0 then replace(Percentage, 0,0.5) end from ##info

I know the above will change the fields whenever they are 0 regardless of the sum of the whole column, and if I do:

select case when sum(Percentage) = 0 then replace(Percentage, 0,0.5) end from ##info

it would throw an error.

Another thing is that I want to replace the temporary table with that column and the above code would only give me the actual column without replacing the original column.

Any suggestions are appreciated

Body:
Please find below answer.

declare @v_val numeric(12,2), @v_new_val numeric(12,2)

set @v_val = 0.00
set @v_new_val = 100

select case when @v_val ='0.00' then @v_new_val else @v_val end