精华内容
下载资源
问答
  • redis——相关问题汇总
    万次阅读 多人点赞
    2019-10-16 10:09:19

    什么是redis?


    Redis 本质上是一个 Key-Value 类型的内存数据库,  整个数据库加载在内存当中进行操作, 定期通过异步操作把数据库数据 flush 到硬盘上进行保存。

    因为是纯内存操作, Redis 的性能非常出色, 每秒可以处理超过 10 万次读写操作, 是已知性能
    最快的 Key-Value DB。
    Redis 的出色之处不仅仅是性能, Redis 最大的魅力是支持保存多种数据结构, 此外单个
    value 的最大限制是 1GB, 不像 memcached 只能保存 1MB 的数据, 因此 Redis 可以用
    来实现很多有用的功能,比方说用他的 List 来做 FIFO 双向链表,实现一个轻量级的高性 能
    消息队列服务, 用他的 Set 可以做高性能的 tag 系统等等。

    另外 Redis 也可以对存入的Key-Value 设置 expire 时间, 因此也可以被当作一 个功能加强版的 memcached 来用。
    Redis 的主要缺点是数据库容量受到物理内存的限制, 不能用作海量数据的高性能读写, 因此 Redis 适合的场景主要局限在较小数据量的高性能操作和运算上


     相比 memcached 有哪些优势?

    1. redis支持更丰富的数据类型(支持更复杂的应用场景):Redis不仅仅支持简单的k/v类型的数据,同时还提供list,set,zset,hash等数据结构的存储。memcache支持简单的数据类型,String。
    2. Redis支持数据的持久化,可以将内存中的数据保持在磁盘中,重启的时候可以再次加载进行使用,而Memecache把数据全部存在内存之中。
    3. 集群模式:memcached没有原生的集群模式,需要依靠客户端来实现往集群中分片写入数据;但是 redis 目前是原生支持 cluster 模式的.
    4. Memcached是多线程,非阻塞IO复用的网络模型;Redis使用单线程的多路 IO 复用模型。

    Redis 的全称是什么?


    Remote Dictionary Server。

    支持哪几种数据类型?


    String、 List、 Set、 Sorted Set、 hashes
     

     

    Redis 有哪几种数据淘汰策略?


    noeviction:返回错误当内存限制达到并且客户端尝试执行会让更多内存被使用的命令(大
    部分的写入指令, 但 DEL 和几个例外)
    allkeys-lru: 尝试回收最少使用的键(LRU), 使得新添加的数据有空间存放。
    volatile-lru: 尝试回收最少使用的键(LRU), 但仅限于在过期集合的键,使得新添加的数据
    有空间存放。
    allkeys-random: 回收随机的键使得新添加的数据有空间存放。
    volatile-random: 回收随机的键使得新添加的数据有空间存放,但仅限于在过期集合的键。
    volatile-ttl: 回收在过期集合的键, 并且优先回收存活时间(TTL) 较短的键,使得新添加的
    数据有空间存放
     

    redis为什么采用跳表而不是红黑树

    在做范围查找的时候,平衡树比skiplist操作要复杂。在平衡树上,我们找到指定范围的小值之后,还需要以中序遍历的顺序继续寻找其它不超过大值的节点。如果不对平衡树进行一定的改造,这里的中序遍历并不容易实现。而在skiplist上进行范围查找就非常简单,只需要在找到小值之后,对第1层链表进行若干步的遍历就可以实现。
    平衡树的插入和删除操作可能引发子树的调整,逻辑复杂,而skiplist的插入和删除只需要修改相邻节点的指针,操作简单又快速。
    从内存占用上来说,skiplist比平衡树更灵活一些。一般来说,平衡树每个节点包含2个指针(分别指向左右子树),而skiplist每个节点包含的指针数目平均为1/(1-p),具体取决于参数p的大小。如果像Redis里的实现一样,取p=1/4,那么平均每个节点包含1.33个指针,比平衡树更有优势。
    查找单个key,skiplist和平衡树的时间复杂度都为O(log n),大体相当;而哈希表在保持较低的哈希值冲突概率的前提下,查找时间复杂度接近O(1),性能更高一些。所以我们平常使用的各种Map或dictionary结构,大都是基于哈希表实现的。
    从算法实现难度上来比较,skiplist比平衡树要简单得多。

    介绍一下HyperLogLog?

    HyperLogLog 是一种概率数据结构,用来估算数据的基数。数据集可以是网站访客的 IP 地址,E-mail 邮箱或者用户 ID。

    基数就是指一个集合中不同值的数目,比如 a, b, c, d 的基数就是 4,a, b, c, d, a 的基数还是 4。虽然 a 出现两次,只会被计算一次。

    使用 Redis 统计集合的基数一般有三种方法,分别是使用 Redis 的 HashMap,BitMap 和 HyperLogLog。前两个数据结构在集合的数量级增长时,所消耗的内存会大大增加,但是 HyperLogLog 则不会。

    Redis 的 HyperLogLog 通过牺牲准确率来减少内存空间的消耗,只需要12K内存,在标准误差0.81%的前提下,能够统计2^64个数据。所以 HyperLogLog 是否适合在比如统计日活月活此类的对精度要不不高的场景。

    这是一个很惊人的结果,以如此小的内存来记录如此大数量级的数据基数。


    为什么 Redis 需要把所有数据放到内存中?


    Redis 为了达到最快的读写速度将数据都读到内存中, 并通过异步的方式将数据写入磁盘。
    所以 Redis 具有快速和数据持久化的特征。 如果不将数据放在内存中, 磁盘 I/O 速度为严重
    影响 Redis 的性能。 在内存越来越便宜的今天, Redis 将会越来越受欢迎。

     

    Redis支持的数据类型?

    String字符串:

    格式: set key value

    string类型是二进制安全的。意思是redis的string可以包含任何数据。比如jpg图片或者序列化的对象 。

    string类型是Redis最基本的数据类型,一个键最大能存储512MB。

     

    Hash(哈希)

    格式: hmset name  key1 value1 key2 value2

    Redis hash 是一个键值(key=>value)对集合。

    Redis hash是一个string类型的field和value的映射表,hash特别适合用于存储对象。

     

    List(列表)

    Redis 列表是简单的字符串列表,按照插入顺序排序。你可以添加一个元素到列表的头部(左边)或者尾部(右边)

    格式: lpush  name  value

    在 key 对应 list 的头部添加字符串元素

    格式: rpush  name  value

    在 key 对应 list 的尾部添加字符串元素

    格式: lrem name  index

    key 对应 list 中删除 count 个和 value 相同的元素

    格式: llen name  

    返回 key 对应 list 的长度

     

    Set(集合)

    格式: sadd  name  value

    Redis的Set是string类型的无序集合。

    集合是通过哈希表实现的,所以添加,删除,查找的复杂度都是O(1)。

     

    zset(sorted set:有序集合)

    格式: zadd  name score value

    Redis zset 和 set 一样也是string类型元素的集合,且不允许重复的成员。

    不同的是每个元素都会关联一个double类型的分数。redis正是通过分数来为集合中的成员进行从小到大的排序。

    zset的成员是唯一的,但分数(score)却可以重复。

     

     sds相对c的改进?

      获取长度:c字符串并不记录自身长度,所以获取长度只能遍历一遍字符串,redis直接读取len即可。

        缓冲区安全:c字符串容易造成缓冲区溢出,比如:程序员没有分配足够的空间就执行拼接操作。而redis会先检查sds的空间是否满足所需要求,如果不满足会自动扩充。

        内存分配:由于c不记录字符串长度,对于包含了n个字符的字符串,底层总是一个长度n+1的数组,每一次长度变化,总是要对这个数组进行一次内存重新分配的操作。因为内存分配涉及复杂算法并且可能需要执行系统调用,所以它通常是比较耗时的操作。   
     

    redis链表源码?有什么特性?

     

    双端、无环、带长度记录、

    多态:使用 void* 指针来保存节点值, 可以通过 dup 、 free 、 match 为节点值设置类型特定函数, 可以保存不同类型的值。

    字典是如何实现的?

    其实字典这种数据结构也内置在很多高级语言中,但是c语言没有,所以redis自己实现了。

    应用也比较广泛,比如redis的数据库就是字典实现的。不仅如此,当一个哈希键包含的键值对比较多,或者都是很长的字符串,redis就会用字典作为哈希键的底层实现。

    LRU?redis里的具体实现?

    LRU全称是Least Recently Used,即最近最久未使用的意思。

    LRU算法的设计原则是:如果一个数据在最近一段时间没有被访问到,那么在将来它被访问的可能性也很小。也就是说,当限定的空间已存满数据时,应当把最久没有被访问到的数据淘汰。

    redis原始的淘汰算法简单实现:当需要淘汰一个key时,随机选择3个key,淘汰其中间隔时间最长的key。**基本上,我们随机选择key,淘汰key效果很好。后来随机3个key改成一个配置项"N随机key"。但把默认值提高改成5个后效果大大提高。考虑到它的效果,你根本不用修改他。

    redis的持久化?

    RDB持久化可以手动执行,也可以配置定期执行,可以把某个时间的数据状态保存到RDB文件中,反之,我们可以用RDB文件还原数据库状态。

    AOF持久化是通过保存服务器执行的命令来记录状态的。还原的时候再执行一遍即可。

     

    如何选择合适的持久化方式?


    一般来说, 如果想达到足以媲美 PostgreSQL 的数据安全性, 你应该同时使用两种持久
    化功能。 如果你非常关心你的数据, 但仍然可以承受数分钟以内的数据丢失, 那么你可以
    只使用 RDB 持久化。
    有很多用户都只使用 AOF 持久化, 但并不推荐这种方式: 因为定时生成 RDB 快照
    (snapshot) 非常便于进行数据库备份, 并且 RDB 恢复数据集的速度也要比 AOF 恢复
    的速度要快, 除此之外, 使用 RDB 还可以避免之前提到的 AOF 程序的 bug。
     

    Redis 集群方案应该怎么做? 都有哪些方案?


    1.twemproxy, 大概概念是, 它类似于一个代理方式, 使用方法和普通 Redis 无任何区别,
    设 置 好它 下 属 的多 个 Redis 实 例 后, 使 用 时在 本 需 要 连接 Redis 的 地 方改 为 连接
    twemproxy, 它会以一个代理的身份接收请求并使用一致性 hash 算法, 将请求转接到具
    体 Redis, 将结果再返回 twemproxy。 使用方式简便(相对 Redis 只需修改连接端口), 对
    旧项目扩展的首选。 问题: twemproxy 自身单端口实例的压力, 使用一致性 hash 后, 对
    Redis 节点数量改变时候的计算值的改变, 数据无法自动移动到新的节点。
    2. codis, 目前用的最多的集群方案, 基本和 twemproxy 一致的效果, 但它支持在 节点
    数量改变情况下, 旧节点数据可恢复到新 hash 节点。
    3. Redis cluster3.0 自带的集群, 特点在于他的分布式算法不是一致性 hash, 而是 hash
    槽的概念, 以及自身支持节点设置从节点。 具体看官方文档介绍。
    4.在业务代码层实现, 起几个毫无关联的 Redis 实例, 在代码层, 对 key 进行 hash 计算,
    然后去对应的 Redis 实例操作数据。 这种方式对 hash 层代码要求比较高, 考虑部分包括,
    节点失效后的替代算法方案, 数据震荡后的自动脚本恢复, 实例的监控, 等等


    MySQL 里有 2000w 数据, Redis 中只存 20w 的数据,

    如何保证 Redis 中的数据都是热点数据?


    Redis 内存数据集大小上升到一定大小的时候, 就会施行数据淘汰策略

    Redis 有哪些适合的场景?


    (1)、 会话缓存(Session Cache)
    最常用的一种使用 Redis 的情景是会话缓存(session cache)。 用 Redis 缓存会话比其他
    存储(如 Memcached) 的优势在于: Redis 提供持久化。 当维护一个不是严格要求一致性
    的缓存时, 如果用户的购物车信息全部丢失, 大部分人都会不高兴的, 现在, 他们还会这样
    吗?
    幸运的是, 随着 Redis 这些年的改进, 很容易找到怎么恰当的使用 Redis 来缓存会话的文
    档。 甚至广为人知的商业平台 Magento 也提供 Redis 的插件。
    (2)、 全页缓存(FPC)
    除基本的会话 token 之外, Redis 还提供很简便的 FPC 平台。 回到一致性问题, 即使重启
    了 Redis 实例, 因为有磁盘的持久化, 用户也不会看到页面加载速度的下降, 这是一个极
    大改进, 类似 PHP 本地 FPC。
    再次以 Magento 为例, Magento 提供一个插件来使用 Redis 作为全页缓存后端。
    此外, 对 WordPress 的用户来说, Pantheon 有一个非常好的插件 wp-Redis, 这个插件
    能帮助你以最快速度加载你曾浏览过的页面。
    (3)、 队列
    Reids 在内存存储引擎领域的一大优点是提供 list 和 set 操作,这使得 Redis 能作为一个
    很好的消息队列平台来使用。 Redis 作为队列使用的操作, 就类似于本地程序语言(如
    Python) 对 list 的 push/pop 操作。
    如果你快速的在 Google 中搜索“Redis queues”, 你马上就能找到大量的开源项目, 这些
    项目的目的就是利用 Redis 创建非常好的后端工具, 以满足各种队列需求。 例如, Celery
    有一个后台就是使用 Redis 作为 broker, 你可以从这里去查看。
    (4)、 排行榜/计数器
    Redis在内存中对数字进行递增或递减的操作实现的非常好。集合(Set)和有序集合(Sorted
    Set) 也使得我们在执行这些操作的时候变的非常简单, Redis 只是正好提供了这两种数据
    结构。 所以, 我们要从排序集合中获取到排名最靠前的 10 个用户–我们称之为
    “user_scores”, 我们只需要像下面一样执行即可:
    当然, 这是假定你是根据你用户的分数做递增的排序。 如果你想返回用户及用户的分数, 你
    需要这样执行:
    ZRANGE user_scores 0 10 WITHSCORES
    Agora Games 就是一个很好的例子, 用 Ruby 实现的, 它的排行榜就是使用 Redis 来存储
    数据的, 你可以在这里看到。
    (5)、 发布/订阅
    最后 是 Redis 的发布/订阅功能。 发布/订阅的使用场景确实非
    常多。 我已看见人们在社交网络连接中使用, 还可作为基于发布/订阅的脚本触发器, 甚至
    用 Redis 的发布/订阅功能来建立聊天系统。


    说说 Redis 哈希槽的概念?


    Redis 集群没有使用一致性 hash,而是引入了哈希槽的概念, Redis 集群有 16384 个哈希槽,
    每个 key 通过 CRC16 校验后对 16384 取模来决定放置哪个槽, 集群的每个节点负责一部分
    hash 槽

    为什么Redis集群有16384个槽

    (1)如果槽位为65536,发送心跳信息的消息头达8k,发送的心跳包过于庞大。

    如上所述,在消息头中,最占空间的是myslots[CLUSTER_SLOTS/8]。 当槽位为65536时,这块的大小是: 65536÷8÷1024=8kb 因为每秒钟,redis节点需要发送一定数量的ping消息作为心跳包,如果槽位为65536,这个ping消息的消息头太大了,浪费带宽。

    (2)redis的集群主节点数量基本不可能超过1000个。

    如上所述,集群节点越多,心跳包的消息体内携带的数据越多。如果节点过1000个,也会导致网络拥堵。因此redis作者,不建议redis cluster节点数量超过1000个。 那么,对于节点数在1000以内的redis cluster集群,16384个槽位够用了。没有必要拓展到65536个。

    (3)槽位越小,节点少的情况下,压缩比高

    Redis主节点的配置信息中,它所负责的哈希槽是通过一张bitmap的形式来保存的,在传输过程中,会对bitmap进行压缩,但是如果bitmap的填充率slots / N很高的话(N表示节点数),bitmap的压缩率就很低。 如果节点数很少,而哈希槽数量很多的话,bitmap的压缩率就很低。

    Redis 集群会有写操作丢失吗? 为什么?


    Redis 并不能保证数据的强一致性, 这意味这在实际中集群在特定的条件下可能会丢失写操
    作。
     

    Redis 集群方案应该怎么做?都有哪些方案?


    1.twemproxy,大概概念是,它类似于一个代理方式, 使用时在本需要连接 redis 的地方改为连接 twemproxy, 它会以一个代理的身份接收请求并使用一致性 hash 算法,将请求转接到具体 redis,将结果再返回 twemproxy。
    缺点: twemproxy 自身单端口实例的压力,使用一致性 hash 后,对 redis 节点数量改变时候的计算值的改变,数据无法自动移动到新的节点。

    2.codis,目前用的最多的集群方案,基本和 twemproxy 一致的效果,但它支持在 节点数量改变情况下,旧节点数据可恢复到新 hash 节点

    3.redis cluster3.0 自带的集群,特点在于他的分布式算法不是一致性 hash,而是 hash 槽的概念,以及自身支持节点设置从节点。具体看官方文档介绍。
     

    为什么要做 Redis 分区?


    分区可以让 Redis 管理更大的内存, Redis 将可以使用所有机器的内存。 如果没有分区, 你
    最多只能使用一台机器的内存。 分区使 Redis 的计算能力通过简单地增加计算机得到成倍提
    升,Redis 的网络带宽也会随着计算机和网卡的增加而成倍增长。
     

    Redis 分区有什么缺点?


    涉及多个 key 的操作通常不会被支持。 例如你不能对两个集合求交集, 因为他们可能被存
    储到不同的 Redis 实例(实际上这种情况也有办法, 但是不能直接使用交集指令)。
    同时操作多个 key,则不能使用 Redis 事务.
    分区使用的粒度是key,不能使用一个非常长的排序key存储一个数据集(The partitioning
    granularity is the key, so it is not possible to shard a dataset with a single huge
    key like a very big sorted set) .
    当使用分区的时候, 数据处理会非常复杂, 例如为了备份你必须从不同的 Redis 实例和主
    机同时收集 RDB / AOF 文件。
    分区时动态扩容或缩容可能非常复杂。 Redis 集群在运行时增加或者删除 Redis 节点, 能
    做到最大程度对用户透明地数据再平衡,但其他一些客户端分区或者代理分区方法则不支持
    这种特性。 然而, 有一种预分片的技术也可以较好的解决这个问题。

     

    Redis 与其他 key-value 存储有什么不同?


    Redis 有着更为复杂的数据结构并且提供对他们的原子性操作,这是一个不同于其他数据库
    的进化路径。 Redis 的数据类型都是基于基本数据结构的同时对程序员透明, 无需进行额外
    的抽象。
    Redis 运行在内存中但是可以持久化到磁盘,所以在对不同数据集进行高速读写时需要权衡
    内存, 应为数据量不能大于硬件内存。 在内存数据库方面的另一个优点是, 相比在磁盘上
    相同的复杂的数据结构, 在内存中操作起来非常简单, 这样 Redis 可以做很多内部复杂性
    很强的事情。 同时, 在磁盘格式方面他们是紧凑的以追加的方式产生的, 因为他们并不需
    要进行随机访问


    Redis 的内存用完了会发生什么?


    如果达到设置的上限, Redis 的写命令会返回错误信息(但是读命令还可以正常返回。) 或
    者你可以将 Redis 当缓存来使用配置淘汰机制,当 Redis 达到内存上限时会冲刷掉旧的内容。
     

    Redis 是单线程的, 如何提高多核 CPU 的利用率?


    可以在同一个服务器部署多个 Redis 的实例, 并把他们当作不同的服务器来使用, 在某些时
    候, 无论如何一个服务器是不够的,
    所以, 如果你想使用多个 CPU, 你可以考虑一下分片(shard)。


    一个 Redis 实例最多能存放多少的 keys? List、 Set、Sorted Set 他们最多能存放多少元素?


    理论上 Redis 可以处理多达 232 的 keys, 并且在实际中进行了测试, 每个实例至少存放了 2亿 5 千万的 keys。 我们正在测试一些较大的值。
    任何 list、 set、 和 sorted set 都可以放 232 个元素。
    换句话说, Redis 的存储极限是系统中的可用内存值

     

    修改配置不重启 Redis 会实时生效吗?


    针对运行实例, 有许多配置选项可以通过 CONFIG SET 命令进行修改, 而无需执行任何
    形式的重启。 从 Redis 2.2 开始, 可以从 AOF 切换到 RDB 的快照持久性或其他方式
    而不需要重启 Redis。 检索 ‘CONFIG GET *’ 命令获取更多信息。
    但偶尔重新启动是必须的, 如为升级 Redis 程序到新的版本, 或者当你需要修改某些目前
    CONFIG 命令还不支持的配置参数的时候
     

    哨兵

    Redis sentinel 是一个分布式系统中监控 redis 主从服务器,并在主服务器下线时自动进行故障转移。其中三个特性:

    监控(Monitoring):    Sentinel  会不断地检查你的主服务器和从服务器是否运作正常。

    提醒(Notification): 被监控的某个 Redis 服务器出现问题时, Sentinel 可以通过 API 向管理员或者其他应用程序发送通知。

    自动故障迁移(Automatic failover): 当一个主服务器不能正常工作时, Sentinel 会开始一次自动故障迁移操作。

    特点:

    1、保证高可用

    2、监控各个节点

    3、自动故障迁移

    缺点:主从模式,切换需要时间丢数据

    没有解决 master 写的压力

     

    缓存穿透


    一般的缓存系统,都是按照key去缓存查询,如果不存在对应的value,就去后端系统查找(比如DB)。

    一些恶意的请求会故意查询不存在的key,请求量很大,就会对后端系统造成很大的压力。这就叫做缓存穿透。

     

    如何避免?

    1:对查询结果为空的情况也进行缓存,这样,再次访问时,缓存层会直接返回空值。缓存时间设置短一点,或者该key对应的数据insert了之后清理缓存。

    2:对一定不存在的key进行过滤。具体请看布隆过滤器

     

    缓存击穿


    是针对缓存中没有但数据库有的数据。

    场景是,当Key失效后,假如瞬间突然涌入大量的请求,来请求同一个Key,这些请求不会命中Redis,都会请求到DB,导致数据库压力过大,甚至扛不住,挂掉。

    解决办法

    1、设置热点Key,自动检测热点Key,将热点Key的过期时间加大或者设置为永不过期,或者设置为逻辑上永不过期

    2、加互斥锁。当发现没有命中Redis,去查数据库的时候,在执行更新缓存的操作上加锁,当一个线程访问时,其它线程等待,这个线程访问过后,缓存中的数据会被重建,这样其他线程就可以从缓存中取值。

     

    缓存雪崩


    是指大量Key同时失效,对这些Key的请求又会打到DB上,同样会导致数据库压力过大甚至挂掉。

    解决办法

    1)让Key的失效时间分散开,可以在统一的失效时间上再加一个随机值,或者使用更高级的算法分散失效时间。

    2)构建多个redis实例,个别节点挂了还有别的可以用。

    3)多级缓存:比如增加本地缓存,减小redis压力。

    4)对存储层增加限流措施,当请求超出限制,提供降级服务(一般就是返回错误即可)

     

    单线程的redis为什么这么快

    (一)纯内存操作
    (二)单线程操作,避免了频繁的上下文切换
    (三)采用了非阻塞I/O多路复用机制

    (其实就是历史遗留问题,非要吹的这么好。。。)

     

    redis采用的删除策略

     

    redis采用的是定期删除+惰性删除策略。


    为什么不用定时删除策略?


    定时删除,用一个定时器来负责监视key,过期则自动删除。虽然内存及时释放,但是十分消耗CPU资源。在大并发请求下,CPU要将时间应用在处理请求,而不是删除key,因此没有采用这一策略.


    定期删除+惰性删除是如何工作的呢?


    定期删除,redis默认每个100ms检查,是否有过期的key,有过期key则删除。需要说明的是,redis不是每个100ms将所有的key检查一次,而是随机抽取进行检查(如果每隔100ms,全部key进行检查,redis岂不是卡死)。因此,如果只采用定期删除策略,会导致很多key到时间没有删除。
    于是,惰性删除派上用场。也就是说在你获取某个key的时候,redis会检查一下,这个key如果设置了过期时间那么是否过期了?如果过期了此时就会删除。

     

    为什么Redis的操作是原子性的,怎么保证原子性的?


    对于Redis而言,命令的原子性指的是:一个操作的不可以再分,操作要么执行,要么不执行。
    Redis的操作之所以是原子性的,是因为Redis是单线程的。
    Redis本身提供的所有API都是原子操作,Redis中的事务其实是要保证批量操作的原子性。
    多个命令在并发中也是原子性的吗?
    不一定, 将get和set改成单命令操作,incr 。使用Redis的事务,或者使用Redis+Lua==的方式实现.
     

    消息队列

    不要使用redis去做消息队列,这不是redis的设计目标。但实在太多人使用redis去做去消息队列,redis的作者看不下去。

    kafka才好用


     

    更多相关内容
  • 在这里,整理发布了年度部门安全存在缺陷汇总表,只为方便大家用于学习、参考,喜欢年度部门安...该文档为年度部门安全存在缺陷汇总表,是一份很不错的参考资料,具有较高参考价值,感兴趣的可以下载看看
  • j4各工种存在质量及违规处罚汇总表.zip
  • 参考资料-j4各工种存在质量及违规处罚汇总表.zip
  • 智能建筑系统(分部工程)检测汇总表 C 2.3.10.13 编号: 工程名称 施工单位 系统名称 检测日期 序号 子系统名称 检测内容 存在问题 检测结果 合格 不合格
  • 煤矿安全生产保障措施作业单日汇总表 时间2013年10月20日 负责人签字 班次 序号 队组名称 作业内容 出勤 作业单 申请人 作业单签发人 调度值班许可 现场安全许可 是否 闭合 零班 1 5 2 3 4 5 6 7 8 早班 1 2 3 4 5 6...
  • Pandas自动分割汇总表写入到子表中

    万次阅读 多人点赞 2021-01-08 23:23:40
    我整理了一下需求大概就是下面的意思,根据汇总表的分区字段自动填入指定的分区文件中: 对于分区表的文件,例如A区.xlsx、B区.xlsx等,需要先将3行之后已经存在的数据删除后再进行写入。 B区.xlsx在自动填入后,...

    作者简介:小小明,Pandas数据处理专家,致力于帮助无数数据从业者解决数据处理难题。

    这是半年前写的一篇文章,里面涉及的方法可能有些过时,但处理思想仍有较高的参考价值,现在发布到csdn。

    群友反馈:

    image-20200612144917595

    我整理了一下需求大概就是下面的意思,根据汇总表的分区字段自动填入指定的分区文件中:

    image-20200612150001917

    对于分区表的文件,例如A区.xlsx、B区.xlsx等,需要先将3行之后已经存在的数据删除后再进行写入。

    B区.xlsx在自动填入后,结果如下:

    image-20200612151046141

    其实初始需求非常简单,我们下面看看怎么做吧。

    基本需求的解决方案

    汇总表的数据情况

    import pandas as pd
    data = pd.read_excel("汇总.xlsx", sheet_name='明细')
    data
    
    所属区身份证号银行卡号姓名开户行金额摘要用途收款账号联行号收款账号省份
    0B区111111NaN张一招商银行576.00NaNNaNNaN河南省
    1B区111112NaN张二招商银行576.00NaNNaNNaN湖南省
    2B区111113NaN张三招商银行1392.00NaNNaNNaN河南省
    3C区2222NaN一一招商银行198.00NaNNaNNaN河南省
    4A区222222NaN二二招商银行671.00NaNNaNNaN云南省
    5A区123444NaN李四中国银行330.00NaNNaNNaN云南省
    6D区123445NaN王五中国银行265.35NaNNaNNaN广东省
    7D区123446NaN赵六中国银行265.35NaNNaNNaN广东省
    8D区123447NaN七七中国银行90.00NaNNaNNaN广东省

    对于B区的数据如何写入呢?

    筛选出准备写入B区的数据

    df = data[data["所属区"] == "B区"]
    df
    
    所属区身份证号银行卡号姓名开户行金额摘要用途收款账号联行号收款账号省份
    0B区111111NaN张一招商银行576.0NaNNaNNaN河南省
    1B区111112NaN张二招商银行576.0NaNNaNNaN湖南省
    2B区111113NaN张三招商银行1392.0NaNNaNNaN河南省
    df = df.iloc[:, 1:]
    df.values.tolist()
    
    [[111111, nan, '张一', '招商银行', 576.0, nan, nan, nan, '河南省'],
     [111112, nan, '张二', '招商银行', 576.0, nan, nan, nan, '湖南省'],
     [111113, nan, '张三', '招商银行', 1392.0, nan, nan, nan, '河南省']]
    

    覆盖写入到对应的分区文件

    workbook = load_workbook(filename="B区.xlsx")
    sheet = workbook.active
    # 先删除第4行之后的旧数据,预计1000行完全够用
    sheet.delete_rows(idx=4, amount=1000)
    # 然后在进行添加数据
    for row in df.values.tolist():
        sheet.append(row)
    workbook.save(filename="B区.xlsx")
    workbook.close()
    

    遍历分区字段的简单办法

    for area, df in data.groupby('所属区'):
        print(area)
        display(df)
    
    A区
    
    所属区身份证号银行卡号姓名开户行金额摘要用途收款账号联行号收款账号省份
    4A区222222NaN二二招商银行671.0NaNNaNNaN云南省
    5A区123444NaN李四中国银行330.0NaNNaNNaN云南省
    B区
    
    所属区身份证号银行卡号姓名开户行金额摘要用途收款账号联行号收款账号省份
    0B区111111NaN张一招商银行576.0NaNNaNNaN河南省
    1B区111112NaN张二招商银行576.0NaNNaNNaN湖南省
    2B区111113NaN张三招商银行1392.0NaNNaNNaN河南省
    C区
    
    所属区身份证号银行卡号姓名开户行金额摘要用途收款账号联行号收款账号省份
    3C区2222NaN一一招商银行198.0NaNNaNNaN河南省
    D区
    
    所属区身份证号银行卡号姓名开户行金额摘要用途收款账号联行号收款账号省份
    6D区123445NaN王五中国银行265.35NaNNaNNaN广东省
    7D区123446NaN赵六中国银行265.35NaNNaNNaN广东省
    8D区123447NaN七七中国银行90.00NaNNaNNaN广东省

    基本需求的整体代码

    import pandas as pd
    data = pd.read_excel("汇总.xlsx", sheet_name='明细')
    
    for area, df in data.groupby('所属区'):
        print(area)
        if os.path.exists(f"{area}.xlsx"):
            workbook = load_workbook(filename=f"{area}.xlsx")
        else:
            print(f"{area}.xlsx不存在")
            continue
        sheet = workbook.active
        df = df.iloc[:, 1:]
        # 先删除第4行之后的旧数据,预计1000行完全够用
        sheet.delete_rows(idx=4, amount=1000)
        # 然后在进行添加数据
        for row in df.values.tolist():
            sheet.append(row)
            print(row)
        print(f"保存到{area}.xlsx文件中")
        workbook.save(filename=f"{area}.xlsx")
        workbook.close()
    
    A区
    [222222, nan, '二二', '招商银行', 671.0, nan, nan, nan, '云南省']
    [123444, nan, '李四', '中国银行', 330.0, nan, nan, nan, '云南省']
    保存到A区.xlsx文件中
    B区
    [111111, nan, '张一', '招商银行', 576.0, nan, nan, nan, '河南省']
    [111112, nan, '张二', '招商银行', 576.0, nan, nan, nan, '湖南省']
    [111113, nan, '张三', '招商银行', 1392.0, nan, nan, nan, '河南省']
    保存到B区.xlsx文件中
    C区
    [2222, nan, '一一', '招商银行', 198.0, nan, nan, nan, '河南省']
    保存到C区.xlsx文件中
    D区
    [123445, nan, '王五', '中国银行', 265.35, nan, nan, nan, '广东省']
    [123446, nan, '赵六', '中国银行', 265.35, nan, nan, nan, '广东省']
    [123447, nan, '七七', '中国银行', 90.0, nan, nan, nan, '广东省']
    保存到D区.xlsx文件中
    

    好了经过以上步骤,就成功完成任务了,群友也表示感谢:

    image-20200612161909000

    扩展需求的实现

    不过好景不长,大早上新需求又来了:

    image-20200612162525397

    现在的需求跟之前的区别有:

    汇总表多了级别字段,需要根据不同的级别对应不同的文件夹

    image-20200612163155133

    所属区的xlsx文件有时可能是xls,并不一定是xlsx的。

    各级别文件夹中存在一些不能匹配汇总表的垃圾文件需要删除。

    汇总表中所有的对应项目并不是都在级别文件夹中存在,不存在的只提示哪些不存在,无需额外处理。

    下面是我的实现过程:

    数据加载

    import os
    import pandas as pd
    
    excel_dir=r"D:\hdfs\excel\汇总表拆分到分区表"
    
    data = pd.read_excel(f"{excel_dir}/汇总.xlsx", sheet_name='明细')
    data
    
    级别所属区身份证号银行卡号姓名开户行金额摘要用途收款账号联行号收款账号省份
    0一级B区111111NaN张一招商银行576.00NaNNaNNaN河南省
    1一级B区111112NaN张二招商银行576.00NaNNaNNaN湖南省
    2一级B区111113NaN张三招商银行1392.00NaNNaNNaN河南省
    3二级C区2222NaN一一招商银行198.00NaNNaNNaN河南省
    4二级A区222222NaN二二招商银行671.00NaNNaNNaN云南省
    5二级A区123444NaN李四中国银行330.00NaNNaNNaN云南省
    6三级D区123445NaN王五中国银行265.35NaNNaNNaN广东省
    7三级D区123446NaN赵六中国银行265.35NaNNaNNaN广东省
    8三级E区123447NaN七七中国银行90.00NaNNaNNaN广东省

    遍历计算出每个级别所涉及的区

    level_areas = {}
    for i, row in data.iterrows():
        areas = level_areas.setdefault(row['级别'], set())
        areas.add(row['所属区'])
    level_areas
    
    {'一级': {'B区'}, '二级': {'A区', 'C区'}, '三级': {'D区', 'E区'}}
    

    删除级别文件夹多余的文件并将xls转换为xlsx

    win32com.client需要通过

    pip install pywin32
    

    安装后使用

    import win32com.client as win32
    
    for level in data['级别'].unique():
        areas = level_areas[level]
        files = os.listdir(f"{excel_dir}/{level}")
        for file in files:
            tag = file.replace(".xlsx", "").replace(".xls", "")
            filename = f"{excel_dir}/{level}/{file}"
            if not tag in areas:
                print(f"删除文件:{filename}")
                os.remove(filename)
            elif file.endswith(".xls"):
                print(f"将 {filename} 转换为 {filename}x")
                excel = win32.gencache.EnsureDispatch('Excel.Application')
                try:
                    wb = excel.Workbooks.Open(filename)
                    wb.SaveAs(f"{filename}x", FileFormat=51)  #FileFormat = 51 is for .xlsx extension
                    print("转换成功")
                finally:
                    wb.Close()  #FileFormat = 56 is for .xls extension
                excel.Application.Quit()
                os.remove(filename)
    
    准备将 D:\hdfs\excel\汇总表拆分到分区表/一级/B区.xls 转换为 D:\hdfs\excel\汇总表拆分到分区表/一级/B区.xlsx
    转换成功
    

    注意:使用pywin32转换excel文件格式时,绝对路径的盘符后面的分隔符必须是反斜杠\,后面的路径分隔符用正斜杠或反斜杠都可以。

    遍历出级别和区域

    for (level, area), df in data.groupby(['级别', '所属区']):
        print(level, area)
        df = df.iloc[:, 2:]
        display(df)
    
    一级 B区
    
    身份证号银行卡号姓名开户行金额摘要用途收款账号联行号收款账号省份
    0111111NaN张一招商银行576.0NaNNaNNaN河南省
    1111112NaN张二招商银行576.0NaNNaNNaN湖南省
    2111113NaN张三招商银行1392.0NaNNaNNaN河南省
    三级 D区
    
    身份证号银行卡号姓名开户行金额摘要用途收款账号联行号收款账号省份
    6123445NaN王五中国银行265.35NaNNaNNaN广东省
    7123446NaN赵六中国银行265.35NaNNaNNaN广东省
    三级 E区
    
    身份证号银行卡号姓名开户行金额摘要用途收款账号联行号收款账号省份
    8123447NaN七七中国银行90.0NaNNaNNaN广东省
    二级 A区
    
    身份证号银行卡号姓名开户行金额摘要用途收款账号联行号收款账号省份
    4222222NaN二二招商银行671.0NaNNaNNaN云南省
    5123444NaN李四中国银行330.0NaNNaNNaN云南省
    二级 C区
    
    身份证号银行卡号姓名开户行金额摘要用途收款账号联行号收款账号省份
    32222NaN一一招商银行198.0NaNNaNNaN河南省

    写出结果

    from openpyxl import load_workbook
    
    for (level, area), df in data.groupby(['级别', '所属区']):
        print(level, area)
        df = df.iloc[:, 2:]
        out_file_name = f"{excel_dir}/{level}/{area}.xlsx"
        if not os.path.exists(out_file_name):
            print(out_file_name, "文件不存在,跳过")
            continue
        print("准备写出到:", out_file_name)
        workbook = load_workbook(filename=out_file_name)
        sheet = workbook.active
        # 先删除第4行之后的旧数据,预计1000行完全够用
        sheet.delete_rows(idx=4, amount=1000)
        # 然后再进行添加数据
        for row in df.values.tolist():
            sheet.append(row)
            print(row)
        print(f"保存到{out_file_name}文件中")
        workbook.save(filename=out_file_name)
        workbook.close()
    
    一级 B区
    准备写出到: D:\hdfs\excel\汇总表拆分到分区表/一级/B区.xlsx
    [111111, nan, '张一', '招商银行', 576.0, nan, nan, nan, '河南省']
    [111112, nan, '张二', '招商银行', 576.0, nan, nan, nan, '湖南省']
    [111113, nan, '张三', '招商银行', 1392.0, nan, nan, nan, '河南省']
    保存到D:\hdfs\excel\汇总表拆分到分区表/一级/B区.xlsx文件中
    三级 D区
    准备写出到: D:\hdfs\excel\汇总表拆分到分区表/三级/D区.xlsx
    [123445, nan, '王五', '中国银行', 265.35, nan, nan, nan, '广东省']
    [123446, nan, '赵六', '中国银行', 265.35, nan, nan, nan, '广东省']
    保存到D:\hdfs\excel\汇总表拆分到分区表/三级/D区.xlsx文件中
    三级 E区
    准备写出到: D:\hdfs\excel\汇总表拆分到分区表/三级/E区.xlsx
    [123447, nan, '七七', '中国银行', 90.0, nan, nan, nan, '广东省']
    保存到D:\hdfs\excel\汇总表拆分到分区表/三级/E区.xlsx文件中
    二级 A区
    准备写出到: D:\hdfs\excel\汇总表拆分到分区表/二级/A区.xlsx
    [222222, nan, '二二', '招商银行', 671.0, nan, nan, nan, '云南省']
    [123444, nan, '李四', '中国银行', 330.0, nan, nan, nan, '云南省']
    保存到D:\hdfs\excel\汇总表拆分到分区表/二级/A区.xlsx文件中
    二级 C区
    准备写出到: D:\hdfs\excel\汇总表拆分到分区表/二级/C区.xlsx
    [2222, nan, '一一', '招商银行', 198.0, nan, nan, nan, '河南省']
    保存到D:\hdfs\excel\汇总表拆分到分区表/二级/C区.xlsx文件中
    

    扩展需求的完整实现代码

    import os
    import pandas as pd
    import win32com.client as win32
    from openpyxl import load_workbook
    
    excel_dir = r"D:\hdfs\excel\汇总表拆分到分区表"
    
    data = pd.read_excel(f"{excel_dir}/汇总.xlsx", sheet_name='明细')
    
    # 遍历计算出每个级别所涉及的区
    level_areas = {}
    for i, row in data.iterrows():
        areas = level_areas.setdefault(row['级别'], set())
        areas.add(row['所属区'])
    print("各级别所拥有的区", level_areas)
    
    # 删除级别文件夹多余的文件并将xls转换为xlsx
    for level in data['级别'].unique():
        areas = level_areas[level]
        files = os.listdir(f"{excel_dir}/{level}")
        for file in files:
            tag = file.replace(".xlsx", "").replace(".xls", "")
            filename = f"{excel_dir}/{level}/{file}"
            if not tag in areas:
                print(f"删除文件:{filename}")
                os.remove(filename)
            elif file.endswith(".xls"):
                print(f"将 {filename} 转换为 {filename}x")
                excel = win32.gencache.EnsureDispatch('Excel.Application')
                try:
                    wb = excel.Workbooks.Open(filename)
                    wb.SaveAs(f"{filename}x", FileFormat=51)
                    print("转换成功")
                finally:
                    wb.Close()  #FileFormat = 56 is for .xls extension
                excel.Application.Quit()
                os.remove(filename)
    
    # 写出结果
    for (level, area), df in data.groupby(['级别', '所属区']):
        print(level, area)
        df = df.iloc[:, 2:]
        out_file_name = f"{excel_dir}/{level}/{area}.xlsx"
        if not os.path.exists(out_file_name):
            print(out_file_name, "文件不存在,跳过")
            continue
        print("准备写出到:", out_file_name)
        workbook = load_workbook(filename=out_file_name)
        sheet = workbook.active
        # 先删除第4行之后的旧数据,预计1000行完全够用
        sheet.delete_rows(idx=4, amount=1000)
        # 然后再进行添加数据
        for row in df.values.tolist():
            sheet.append(row)
            print(row)
        print("保存到", out_file_name, "文件中")
        workbook.save(filename=out_file_name)
        workbook.close()
    
    各级别所拥有的区 {'一级': {'B区'}, '二级': {'A区', 'C区'}, '三级': {'D区', 'E区'}}
    一级 B区
    准备写出到: D:\hdfs\excel\汇总表拆分到分区表/一级/B区.xlsx
    [111111, nan, '张一', '招商银行', 576.0, nan, nan, nan, '河南省']
    [111112, nan, '张二', '招商银行', 576.0, nan, nan, nan, '湖南省']
    [111113, nan, '张三', '招商银行', 1392.0, nan, nan, nan, '河南省']
    保存到 D:\hdfs\excel\汇总表拆分到分区表/一级/B区.xlsx 文件中
    三级 D区
    准备写出到: D:\hdfs\excel\汇总表拆分到分区表/三级/D区.xlsx
    [123445, nan, '王五', '中国银行', 265.35, nan, nan, nan, '广东省']
    [123446, nan, '赵六', '中国银行', 265.35, nan, nan, nan, '广东省']
    保存到 D:\hdfs\excel\汇总表拆分到分区表/三级/D区.xlsx 文件中
    三级 E区
    准备写出到: D:\hdfs\excel\汇总表拆分到分区表/三级/E区.xlsx
    [123447, nan, '七七', '中国银行', 90.0, nan, nan, nan, '广东省']
    保存到 D:\hdfs\excel\汇总表拆分到分区表/三级/E区.xlsx 文件中
    二级 A区
    准备写出到: D:\hdfs\excel\汇总表拆分到分区表/二级/A区.xlsx
    [222222, nan, '二二', '招商银行', 671.0, nan, nan, nan, '云南省']
    [123444, nan, '李四', '中国银行', 330.0, nan, nan, nan, '云南省']
    保存到 D:\hdfs\excel\汇总表拆分到分区表/二级/A区.xlsx 文件中
    二级 C区
    准备写出到: D:\hdfs\excel\汇总表拆分到分区表/二级/C区.xlsx
    [2222, nan, '一一', '招商银行', 198.0, nan, nan, nan, '河南省']
    保存到 D:\hdfs\excel\汇总表拆分到分区表/二级/C区.xlsx 文件中
    

    上面只是示例数据,所以看出来有啥效果,但是群友要处理的实际数据有几百个文件夹,用代码处理的优势一下子就体现出来了。看看群友的反馈吧:

    image-20200612183313670

    展开全文
  • 可以通过该工作簿的代码修改成自己想要的工作簿,方便以后每次汇总多个工作簿
  • 常见的DAX函数返回的是一个值,比如SUM,MAX等,称为值函数,与此同时,DAX中还有很多返回函数,比如FILTER,ALL,VALUES,DISTINCT这些都是比较常用的函数,新建度量值、新建列都是需要返回一个值,如果用...

    前言

    常见的DAX函数返回的是一个值,比如SUM,MAX等,称为值函数,与此同时,DAX中还有很多返回表的表函数,比如FILTER,ALL,VALUES,DISTINCT这些都是比较常用的表函数,新建度量值、新建列都是需要返回一个值,如果用返回表的表达式来创建,将会报错,而新表就是利用DAX创建一个表,它使用的就是返回表的表达式。今天我们来介绍的就是除了上述所说的表函数外,还有一些生成笛卡尔积和返回汇总表的表函数,他们也是我们工作中会经常用来做辅助计算的。

    用于生成笛卡尔积表的两个函数

    首先,我们来生成一张销售表,在这个过程中,我们将会简单介绍一下生成笛卡尔积表的函数GENERATE和CROSSJOIN。

    函数示例一:GENERATE应用示例

    销售表 =
    GENERATE (
    ADDCOLUMNS (
    CALENDAR ( DATE ( 2019, 1, 1 ), TODAY () ),
    “月”, MONTH ( [Date] ),
    “销量”, RANDBETWEEN ( 0, 300 )
    ),
    DATATABLE ( “门店”, STRING, { { “北京” }, { “上海” }, { “广州” }, { “深圳” } } )
    )

    在实例的这个函数中,GENERATE,ADDCOLUMNS,DATATABLE,是主要需要说明的三个生成表的函数。

    ADDCOLUMNS函数(DAX)

    ADDCOLUMNS(<table>, <name>, <expression>[,<name>, <expression>]…)
    参数:

    table:任何返回表的 DAX 表达式。
    name:给予此列的名称,包含在双引号内。
    expression:任何返回要填充 name 的单个标量值的 DAX 表达式。

    返回值:

    返回具有DAX表达式指定的新列的表。

    ADDCOLUMNS顾名思义,添加列,在什么上添加列,在某个原始表中添加所需要的列。所以这个函数的第一个参数为表或返回表的表达式,在该表中添加列;第二个参数和第三个参数分别为要添加的列的列名和列值;有的时候只添加一列并不能满足我们的需求,那怎么办呢?诶,ADDCOLUMNS这个函数可以一直往后面添加列,它的第四个参数和第五个参数就分别为要继续添加的列的列名和列值;以此类推……

    ADDCOLUMNS(
            CALENDAR ( DATE ( 2019, 1, 1 ), TODAY() ),
            "月", MONTH ( [Date] ),
            "销量", RANDBETWEEN ( 0, 300 )
    )
    

    就是生成了一个在含有一列日期的日期表的基础上添加了列名为“月”和“销量”的列。如下:
    file

    DATATABLE函数(DAX)

    DATATABLE (ColumnName1, DataType1, ColumnName2, DataType2…,{{Value1, Value2…}, {ValueN, ValueN+1…}…})

    参数:

    ColumnName:任何返回表的 DAX 表达式。
    DataType:数据类型。一个枚举,其中包含:INTEGER、DOUBLE、STRING、BOOLEAN、CURRENCY、DATETIME
    Value:单个自变量。它将 Excel 语法用于一维数组常量,该常量嵌套为提供数组的数组。此参数表示将在表中的一组数据值

    返回值:

    声明内联值集的表。

    DATATABLE顾名思义,数据表,DATATABLE函数的参数:DATATABLE (列名1, 数据类型1, 列名2, 数据类型2…, {{值1, 值2…},{值N, 值N+1…}…})
    前面的参数用来指定 列名 和 这一列的数据类型,后面 {}包含了数据,每一行都放在一对 { } 中,且用逗号分隔。数据类型支持:string,integer,currency,double,datetime,boolean(布尔型,也就是 true 或 false)。

    DATATABLE( "门店", STRING, { { "北京" }, { "上海" }, { "广州" }, { "深圳" } } )

    就是生成了一个列名为“门店”,数据类型为文本型,值集包含了北京上海广州深圳的一列。如下:
    file

    GENERATE 函数 (DAX)

    GENERATE(<table1>, <table2>)

    参数:

    table1任何返回表的 DAX 表达式。
    table2任何返回表的 DAX 表达式。

    返回值:

    一个表以及一个笛卡尔积,后者是在 table1 中的每行与通过在 table1 中的当前行的上下文中计算 table2 所得到的表之间计算获得的。

    GENERATE函数的参数非常简单,就是两个表,但它的内部计算逻辑其实比较复杂,初识这个函数的时候我们只需要了解它并不是简单的分别计算了两张表再合并到一起,而是它第一个参数表的每一行,为第二个表的表达式提供了行上下文,在每一行上分别计算第二个表的表达式。

    GENERATE (
       ADDCOLUMNS (
           CALENDAR ( DATE ( 2019, 1, 1 ), TODAY () ),
           "月", MONTH ( [Date] ),
           "销量", RANDBETWEEN ( 0, 300 )
       ),
       DATATABLE ( "门店", STRING, { {"北京" }, { "上海"}, { "广州" }, { "深圳" } } )
    )
    

    就是生成了一个前两个表相乘的笛卡尔积表。如下:
    file

    函数示例二:CROSSJOIN应用示例

    与GENERATE类似的还有另一个可以生成笛卡尔积表的函数叫做CROSSJION。

    CROSSJOIN函数(DAX)

    CROSSJOIN(<table>,<table>[, <table>]…)

    参数:

    table:任何返回数据表的 DAX 表达式数

    返回值:

    返回一个包含这些参数的所有表中所有行的笛卡尔积的表。新表中的各列是所有参数表中的所有列。

    销售表1 = CROSSJOIN('销售表','dim商品名称')

    CROSSJOIN的参数与GENERATE 函数 的参数类似,就是两个表,返回的结果即为两表相乘的交叉积。在这个公式中,CROSSJOIN的第一个参数是销售表,第二个参数是一列含有商品A,商品B,商品C,商品D的商品名称维度表,交叉相乘结果如下:
    file

    至此,我们已经介绍了四个生成表的函数,ADDCOLUMNS,DATATABLE, CROSSJOIN和 GENERATE,并且写出了一个接下来我们会用到的销售表。

    问题:如果要看每个月每个门店的销售额,返回一个汇总表,有几种计算方法?

    方法一:第一个比较容易想到的返回汇总表的函数应该是SUMMARIZE

    SUMMARIZE函数(DAX)

    SUMMARIZE(<table>,<groupBy_columnName>[, <groupBy_columnName>]…[, <name>,<expression>]…)

    参数:

    Table:任何返回数据表的 DAX 表达式。
    groupBy_columnName:可选)现有列的限定名称,将使用该列中找到的值创建摘要组。此参数不能是表达式。
    Name:给予总计或汇总列的名称,包含在双引号内。
    Expression:任何返回单个标量值的 DAX 表达式,其中,表达式将计算多次(针对每行/上下文)。

    返回值:

    针对一系列组所请求的总计返回摘要表,即其中包含 groupBy_columnName 参数的选定列和由名称参数设计的汇总列的表。

    销售表3 = SUMMARIZE('销售表1','销售表1'[门店],'销售表1'[月],"月销量",SUM('销售表1'[销量]))

    1、SUMMARIZE第一个参数是表,第二个参数是某一列,先不写其他参数列时,会返回该表的不重复列表。用于提取维度表,同VALUES和DISTINCT的用法类似。

    dim门店 = SUMMARIZE(‘销售表’,‘销售表’[门店])
    dim门店 = VALUES(‘销售表’,‘销售表’[门店])
    dim门店 =DISTINCT(‘销售表’,‘销售表’[门店])
    file

    以上表达式都是提取维度表的做法,可以提取到不重复的门店名称。

    2、当我们为SUMMARIZE添加第三个参数列时,它会返回这些列的有效组合,但不同于上述GENERATE或CROSSJOIN返回的是笛卡尔积一样,它返回的只是原销售表中存在的组合。

    file

    3、所以当我们继续为SUMMARIZE添加第三、四个参数分别是列名和列值表达式时,它会自动计算并返回分组的汇总表。如下,也就是我们想得到的每个月每个门店的销售额的汇总表。
    file

    方法二:利用ADDCOLUMNS和CROSSJOIN的组合返回汇总表

    销售表2 = ADDCOLUMNS(CROSSJOIN(VALUES('销售表1'[月]),VALUES('销售表1'[门店])),"月销量",CALCULATE(SUM('销售表1'[销量])))

    在此我们可以巩固一下之前所说的ADDCOLUMNS和CROSSJOIN的用法,CROSSJOIN的两个参数分别是销售表1中的月份和门店,CROSSJOIN生成了月份和门店的笛卡尔积,ADDCOLUMNS为这个笛卡尔积表添加了列名为”月销量”,列值为各月各门店销量总计的列,至此,每个月每个门店的销售额的汇总表的第二个方法我们也完成了。

    • PowerPivot工坊原创文章,转载请注明出处!

    如果您想深入学习微软Power BI,欢迎登录网易云课堂试听学习我们的“从Excel到Power BI数据分析可视化”系列课程。或者关注我们的公众号(PowerPivot工坊)后猛戳”在线学习”。


    长按下方二维码关注“Power Pivot工坊”获取更多微软Power BI、PowerPivot相关文章、资讯,欢迎小伙伴儿们转发分享~

    展开全文
  • Excel表格中汇总多个数据中的数据的方法Excel表格中汇总多个数据中的数据的方法 Excel表格中汇总多个数据中的数据的方法 1.打开需要处理的工作簿,在这个工作簿中,"11月采购"和"12月采购"工作包含需要...

    Excel表格中汇总多个数据表中的数据的方法

    Excel表格中汇总多个数据表中的数据的方法 Excel表格中汇总多个数据表中的数据的方法 1.打开需要处理的工作簿,在这个工作簿中,"11月采购表"和"12月采购表"工作表包含需要合并的数据,如图1所示."合计"工作表用于放置进行合并计算后的数据,其结构如图2所示. 图1 需要合并的两张工作表 图2 "合计"工作表的结构 2.在"合计"工作表中单击选择放置合并计算结果的单元格区域中的第一个单元格,然后在功能区"数据"选项卡的&qu...

    excel

    2016/12/14

    excel通过VLOOKUP函数从一个表中取数到另一个表

    excel通过VLOOKUP函数从一个表中取数到另一个表 Excel表格处理数据的功能很强大,工作中,经常需要从数据库文件中查找索引数据到另一个文件中,如何实现这个引用?通过VLOOKUP函数,就可以轻松做到. VLOOKUP函数是一个纵向查找函数,用于按列进行查找索引数据.我们根据需求,进行设定就能快速完成. 步骤 打开电脑,点开Excel,打开数据库文件和需要引用数据的文件. 现以一份电子账册的料件为例:企业在海关备案了552个不同的商品,这552个"商品名称"有"备案序号",另一份料件表文件只有"备案序号&...

    excel

    2016/05/14

    在Excel2010工作表中给数据创建图片

    在Excel2010工作表中给数据创建图片 在excel2010工作表中创建给数据创建图片,可以让图片根据数据区域的变化而变化,而且可以对数据图片的格式进行单独设置而不影响原数据.今天小编就为大家详细介绍一下,不会的朋友可以参考本文,希望能对大家有所帮助! 方法/步骤 1.在桌面Excel2010图标上双击鼠标左键,将其打开运行.启动Excel2010表格编辑程序.如图所示: 2.在打开的Excel2010工作表编辑窗口,选中需要创建数据图片的区域,点击鼠标右键,在弹出的快捷菜单列表中选择“复制”选项点击它.如图所示: 3.在Excel窗...

    其他

    2016/01/03

    excel如何提取身份证信息中的数据?

    excel如何提取身份证信息中的数据? 近日有读者留言想进一步提取身份证信息中的数据,小编今天就来与大家分享一二. 身份证是一组有规律的号码,具体分割应该是:XXX XXX XXXX XX XX XX X X的格式.这里解释一下,第一组和第二组共六个数字代表的是户籍所在地代码,第三组数据是出生年份,第四组两位数代表出生月份,第五组两位数代表出生日期,倒数第二位则是性别判断数字(偶数为女性,奇数为男性),在统计信息的时候,可能只登记了姓名和身份证号码,如何能够快速的将生日.性别.年龄一一提取计算出来,这是很多人都想知道的.而这些,都可以...

    其他

    2017/01/09

    对Excel数据区域或表中的数据进行排序

    对Excel数据区域或表中的数据进行排序 对Excel数据进行排序是数据分析不可缺少的组成部分。您可能需要执行以下操作:将名称列表按字母顺序排列;按从高到低的顺序编制产品存货水 平列表,按颜色或图标对行进行排序。对数据进行排序有助于快速直观地显示数据并更好地理解数据,有助于组织并查找所需数据,有助于最终做出更有效的决策。 注释 若要查找某个单元格区域或某个表中的上限或下限值(如前 10 名或后 5 个销售额),可以使用自动筛选或条件格式。 了解排序 可以对一列或多列中的数据按文本(升序或降序)、数字(升序或降序)以及日期...

    excel

    2011/08/09

    用公式查找Excel工作表中重复数据

    用公式查找Excel工作表中重复数据 前不久,一位网友求教,要求找出Excel工作表中的重复数据并在单元格中列出。我给出了一个数组公式供参考,但不是太符合要求,因为这个数组公式虽然找出了重复数据,但是如果将数组公式向下复制时超出了出现重复数据的数量,会在相应单元格中显示错误。不久,这位朋友获得了更好的一个公式。这个公式非常好,很好地解决了这类问题,供有兴趣的朋友参考。 在列A和列B中存在一系列数据(表中只是示例,可能数据还有很多),要求找出某人(即列A中的姓名)所对应的所有培训记录(即列B中的数据)。也就是说,在单元格E1中输入某人...

    excel

    2014/04/11

    如何利用Excel2010的"切片器"功能查看透视表中明细数据不切换筛选或工作表方法

    如何利用Excel2010的"切片器"功能查看透视表中明细数据不切换筛选或工作表方法 利用Excel2010的“切片器”功能,你可以快捷查看数据透视表中的某项明细数据,而不用切换工作表或进行筛选操作。 1、启动Excel2010,打开相应的工作簿文档。 2、选中数据透视表中任意一个单元格,切换到“数据透视表工具/选项”功能选项卡中,单击“排序和筛选”组中的“插入切片器”按钮,打开“插入切片器”对话框。 3、选中作为切片器的字段名称(如“省市”、“事故类型”等)选项,单击“确定”按钮,相应的切片器被添加到数据透视表中。 4、在相应的切片器标签中,在“Shift”键或...

    excel

    2013/09/10

    Access表中的数据怎么复制和转移?

    Access表中的数据怎么复制和转移? Access中的数据想要复制和转移,该怎么实现呢?请看下文详细教程. 1.首先打开数据文件. 2.在[创建]功能区的[查询]分组中单击“查询设计”按钮,系统弹出查询设计器. 3.在[显示表]对话框中添加“tStud”表,关闭对话框. 4.单击[查询类型]分组中的“追加”按钮,在追加表对话框内输追加到表名称“tTemp”,单击“确定”按钮关闭对话框. 5.双击“学号”.“姓名”.“年龄”.“所属院系”.“入校时间”.“性别”字段. 6.在“性别”列的“条件”行中输入:"女". 7.点击“...

    其他

    2017/09/30

    筛选Excel 2007单元格区域或表中的数据

    筛选Excel 2007单元格区域或表中的数据 Excel2007使用自动筛选来筛选数据,可以快速而又方便地查找和使用单元格区域或表列中数据的子集。 了解有关筛选的详细信息 筛选过的数据仅显示那些满足指定条件 (条件:所指定的限制查询或筛选的结果集中包含哪些记录的条件。)的行,并隐藏那些不希望显示的行。筛选数据之后,对于筛选过的数据的子集,不需要重新排列或移动就可以复制、查找、编辑、设置格式、制作图表和打印。 您还可以按多个列进行筛选。筛选器是累加的,这意味着每个追加的筛选器都基于当前筛选器,从而进一步减少了数据的子集。 ...

    excel

    2011/08/09

    使用高级条件筛选Excel表中的数据

    使用高级条件筛选Excel表中的数据 若要通过复杂的条件 (条件:为限制查询结果集中包含的记录而指定的条件。例如,以下条件用于选择 Order Amount 字段的值大于 30,000 的记录:Order Amount > 30000。)来筛选单元格区域,请使用“数据”选项卡上“排序和筛选”组中的“高级”命令。“高级”命令的工作方式在几个重要的方面与“筛选”命令有所 不同。 ·它显示了“高级筛选”对话框,而不是“自动筛选”菜单。 ·您可以在工作表以及要筛选的单元格区域或表上的单独条件区域中键入高级条件。Micros...

    excel

    2011/08/08

    在excel中如何跨工作表自动引用数据或计算?

    在excel中如何跨工作表自动引用数据或计算? 在实际工作过程中,我们经常需要编制各种模板供其他人填写.所以,利用数据有效性制作可供选择的下拉菜单就是一个很常用的方法.一般来说,我们会制作一个包含全部基本信息的表,表单列示所有的备选项目,比如说姓名.型号.部门等等,而实际的填写表格是另外一张表.那么在excel中如何跨工作表自动引用数据或计算?下面小编就为大家介绍一下,一起来看看吧! 方法/步骤 暗渠简单的一种是直接等于其它工作表的单元格,比如:sheet2工作表中A1单元格引用sheet1表中A1单元格,则直接=Sheet1!A1就引用过来...

    excel

    2015/09/13

    让Word和Excel表格中的数据同步更新

    让Word和Excel表格中的数据同步更新 在Word文档编辑时,需要用到Excel工作表中的数据资料,但是当word和Excel中某一个数据发生改变后,我们就不得不寻找对应的部分来修改,如果涉及的数据量较大,那工作的强度也会大大提升。其实,Word和Excel提供了能让两者数据同步的方法,并且简单易行。 Step1:复制段落 打开Word文档和要引用该Word数据的Excel工作表,然后在word中回个车,这时显示两个段落标记。选取这两个段落标记,然后复制。 Step2:粘贴链接 切换到Excel编辑窗口,鼠标右键单...

    word

    2012/12/18

    让word和Excel表格中的数据自动同步更新教程

    让word和Excel表格中的数据自动同步更新教程 在Word文档编辑时,需要用到Excel工作表中的数据资料,但是当word和Excel中某一个数据发生改变后,我们就不得不寻找对应的部分来修改,如果涉及的数据量较大,那工作的强度也会大大提升.其实,Word和Excel提供了能让两者数据同步的方法,并且简单易行. Step1:复制段落 打开Word文档和要引用该Word数据的Excel工作表,然后在word中回个车,这时显示两个段落标记.选取这两个段落标记,然后复制. Step2:粘贴链接 切换到Excel编辑窗口,鼠标右键单击数据栏,选择选择性...

    word

    2016/03/05

    Excel怎么快速删除工作表中空白行

    Excel怎么快速删除工作表中空白行 Excel怎么快速删除工作表中空白行 1.启动Excel 2013并打开工作表,这张工作表中存在一些空行.在工作表中选择数据区域,在"开始"选项卡的"编辑"组中单击"排序和筛选"按钮,在打开的下拉列表中选择"筛选"选项,如图1所示. 图1 选择"筛选"选项 2.此时,数据表的列标题右侧将会出现筛选下三角按钮,单击任意一个下三角按钮,在打开的下拉列表中取消对"全选"复选框的勾选,然后勾选&...

    excel

    2016/12/13

    让ppt中的数据动起来的方法

    让ppt中的数据动起来的方法 操作步骤 1、打开PowerPoint2010,在“插入”功能栏中找到“插图”,然后单击“图表”选项。 2、在弹出的“插入图表”窗口左侧的列表框中选择图表类型,然后在右侧礼包中选择子类型,点击“确认”按钮。 3、此时,会自动启动Excel,让用户在工作吧的单元格中直接输入数据。 4、更改工作表中的数据,PowerPoint的图表会自动更新。 5、输入完毕后。关闭Excel,然后用户可以利用“设计”选项卡中的“图表布局”工具与“图表样式”工具快速设置图表格式。...

    其他

    2013/11/17

    应用图表功能让ppt中的数据动起来

    应用图表功能让ppt中的数据动起来 图表是我们在工作中常用到的统计方法,而我们又常用ppt来讲解数据,那如何让两者灵活的结合再一起呢,今天小编就为大家介绍具体操作。 操作步骤 1、打开PowerPoint2010,在“插入”功能栏中找到“插图”,然后单击“图表”选项。 2、在弹出的“插入图表”窗口左侧的列表框中选择图表类型,然后在右侧礼包中选择子类型,点击“确认”按钮。 3、此时,会自动启动Excel,让用户在工作吧的单元格中直接输入数据。 4、更改工作表中的数据,PowerPoint的图表会自动更新。 5、输入完毕后。关闭Exc...

    其他

    2012/11/18

    如何解决Oracle数据库中重复数据的方法步骤

    如何解决Oracle数据库中重复数据的方法步骤 在平时的开发中,我们经常遇到数据表中出现重复的数据,那么该如何解决呢?这里介绍两种情况下的数据去重方法,一、完全重复数据去重;二、部分字段数据重复去重。 一、完全重复数据去重方法 对于表中完全重复数据去重,可以采用以下SQL语句。 Code CREATETABLE"#temp"AS (SELECTDISTINCT * FROM 表名);--创建临时表,并把DISTINCT 去重后的数据插入到临时表中 truncateTABLE 表名;--清空原表数据 INSERTINTO 表名(SELECT *...

    其他

    2013/09/13

    如何对Excel工作表中数据随机排序?

    如何对Excel工作表中数据随机排序? 1.启动Excel并打开工作表,在数据区域外的H2单元格中输入公式"=RAND()",按Enter键获得计算结果.拖动填充柄 将公式填充到该列的其他单元格中,如图1所示. 图1 创建公式并填充到其他单元格中 2.在"开始"选项卡的"编辑"组中单击"排序和筛选"按钮,在打开的菜单中选择"降序"命令,如图2所示.在"排序提醒"对话框中选择"扩展选定区域"单选按钮,单击&q...

    excel

    2016/02/25

    Excel工作表中使用模拟运算表的方法

    Excel工作表中使用模拟运算表的方法 Excel工作表中使用模拟运算表的方法 1.创建一个新的Excel工作表,并在工作表输入数据.在B9单元格中输入提成金额的计算公式"=$B$2*$B$3",如图1所示. 图1 创建工作表并输入公式 2.在B10:B23单元格区域中输入提成比率,然后选择用于创建模拟运算表的单元格B9:I23,在"数据"选项卡的"数据工具"组中单击"模拟分析"按钮,在打开的下拉列表中选择"模拟运算表"选项,如图2所示. 图2 ...

    excel

    2016/12/14

    Excel工作表中怎么插入分类汇总

    Excel工作表中怎么插入分类汇总   Excel工作表中怎么插入分类汇总 1.打开需要创建分类汇总的工作表,选择"员工部门"所在的列后在"数据"选项卡的"排序和筛选"组中单击"降序"按钮,如图1所示.此时Excel 2013会给出"排序提醒"对话框让用户选择排序依据,这里直接单击"排序"按钮关闭对话框,如图2所示. 图1 单击"降序"按钮 图2 "排序提醒"对话框 2.在工作表中选...

    excel

    2016/12/13

    展开全文
  • 桌面运维工程师常见面试问题汇总

    千次阅读 2021-06-25 02:07:25
    2、如何快速判断网络中是否存在环路?3、同一VLAN能否配置多个网段?VLAN间互访不通怎么办?4、如何释放和更新PC上的IP地址?客户端获取的IP地址与其他...1.1 [简单说一下OSI七层] Osi模型是一个工业的标准.它为现在...
  • MySQL语句汇总

    2020-12-14 19:47:45
    文章目录MySQL语句汇总第一章:数据库基础知识:一、对数据库的操作二、数据的基本操作:三、的约束四、设置的字段值自动增加第二章:索引1、创建的时候创建索引1> 创建普通索引2> 创建唯一性索引3> 创建...
  • oracle迁移达梦常见问题汇总

    千次阅读 2020-04-21 11:23:21
    0、说明 本文主要结合之前一次oracle迁移达梦的项目,将碰到的问题以及一系列踩...其中对于出错的任务,主要有导和导函数包两部分。其中主要棘手的在与迁移函数包等对象时的问题。 2、迁移错误详解 2.1、通用问题...
  • Pandas实例|药品发放汇总与excel数据回填

    万次阅读 多人点赞 2021-02-09 12:54:43
    作者:小小明 需求 有一个卫生院需要统计一下每个村扶贫药品发放的数据。 数据形式是在一个文件夹下,每个村的数据都...for name in Path(r"F:\jupyter\test\药品数据汇总\基础").glob("[!~]*.xls*"): filename = .
  • 总而言之,正是由于缓冲区的存在,才使得我们能够多输入一些数据,或者一次性输入所有数据,这可以认为是缓冲区的一点优势。然而,缓冲区也带来了一定的负面影响,甚至会导致很奇怪的行为,请看下面的代码:  ...
  • U8常见问题汇总

    万次阅读 2020-11-26 09:11:39
    U8常见问题汇总 1、帐套如何进行输出 系统管理——账套——输出; 2、帐套如何进行引入 开始——程序——用友ERP-U8——系统管理——操作员admin——密码791121——账套(找到备份的数据)——引入——**账套引入...
  • SAP 表汇总

    千次阅读 2016-07-21 10:05:37
    SAP 整理: VBKPF-预制凭证抬头: VBKPF-预制凭证抬头 VBKPF-预制凭证抬头 VBSEG-预制凭证行项目: VBSEG-预制凭证行项目 VBSEG-预制凭证行项目 VBSEGA-预制凭证行项目_资产: VBSEGA-预制凭证...
  • 计算机考研复试问题汇总(408+计算机前言知识)

    万次阅读 多人点赞 2020-04-12 01:52:28
    视图:是一种虚拟的,具有和物理相同的功能,它可以对试图进行增删改查操作,试图通常是一个或者多个的行或者列的子集,对视图的修改会不影响基本。它使得我们获取数据更容易,相比多查询。 游标:是对...
  • HashMap问题汇总

    万次阅读 2019-09-18 09:20:44
    你如果这个时候说不知道的话,那这块问题就到此结束了。如果你说有TreeMap和LinkedHashMap。 那么面试官接下来就可能会问你,TreeMap和LinkedHashMap是如何保证它的顺序的? 如果你回答不上来,那么到此为止。 ...
  • 数据结构面试经典问题汇总

    万次阅读 多人点赞 2020-05-26 16:23:13
    数据结构面试经典问题汇总参考资源:基础深入补充: 参考资源: 基础 数据结构常见面试题 深入 数据结构面试题(三) 数据结构面试必问 数据结构算法常见面试考题 补充: 1.数组和链表的区别,请详细解释。 从逻辑...
  • C++面试常问问题汇总

    万次阅读 多人点赞 2018-10-16 09:11:25
    编译器为每一个类维护一个虚函数(本质是一个函数指针数组,数组里面存放了一系列函数地址 ),每个对象的首地址保存着该虚函数的指针,同一个类的不同对象实际上指向同一张虚函数。调用形式: *(this 指针 + ...
  • Java 面试问题汇总

    千次阅读 2019-04-30 16:22:24
    面试问题汇总 2019.4月份 ​​ ​​ Java问题汇总 jvm加载类的机制 Jvm是通过classLoader来加载java类文件,jvm会把java中的类加载到内存中去,通过jvm中的解析器把类文件解析成要执行的汇编语言,java中有三大...
  • js知识点汇总

    万次阅读 多人点赞 2019-11-03 20:58:42
    Model 这些对象并不是独立存在的,对象与对象之间存在着层次结构, 对象模型 的作用就是描述这些层次结构。 弹出提示窗口 alert(); 例:window.alert(‘你确定要关闭此窗口吗?’);  弹出确认窗口 confirm() ...
  • 达梦数据库常见问题汇总

    万次阅读 2019-12-24 14:41:27
    问题一 以图形界面 安装数据库软件时报错 现象描述 用图形界面形式安装数据库遇到报错,显示报错信息如下: [dmdba@localhost mnt]$ ./DMInstall.bin 解压安装程序.......... No protocol specified ...
  • c++面试常见问题汇总

    万次阅读 多人点赞 2019-07-01 21:41:34
    c++面试常见问题汇总1. 指针和引用的区别2. 堆栈的区别:3. new 和 delete 是如何实现的,与 malloc 和 free有什么异同?4. struct 和 class 的区别:5. define 和 const 的区别:   1. 指针和引用的区别 (1)...
  • 小米滑板车问题汇总

    万次阅读 2021-02-28 16:12:06
    小米滑板车 爆胎 断电 ...3、电源按钮无反应,或按一次出现多次反应的,且充电正常,使用吹风机加热电量显示部分外壳,取下外壳,观察按键部分是否存在进水、腐蚀,清理干净测试。 4、充电只是灯常亮绿灯,按下电源键不
  • 链表经典问题汇总

    万次阅读 多人点赞 2011-08-09 20:45:05
    6、分别判断两个链表A、B是否有环(注,两个有环链相交是指这个环属于两个链表共有) 如果仅有一个有环,则A、B不可能相交 如果两个都有环,则求出A的环入口,判断其是否在B链表上,如果在,则说明A、...
  • 数据库面试问题汇总(超详细)

    万次阅读 多人点赞 2019-10-19 23:46:11
    第一范式:数据库的每一列都是不可分割的原子数据项,而不能是集合,数组,记录等非原子数据项。eg:【联系人】(姓名,性别,电话),一个联系人有家庭电话和公司电话,那么这种结构设计就没有达到 1NF; 第二...
  • ClickHouse数据库和数据

    千次阅读 2020-09-28 11:17:22
    文章目录ClickHouse数据库和数据大小写敏感数据库数据库操作ClickHouse数据库引擎数据存储目录数据数据操作创建数据复制结构复制结构和数据 ClickHouse数据库和数据 ClickHouse的语法与MySQL很类似,...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 116,706
精华内容 46,682
关键字:

存在问题汇总表